Remove duplicate, CTE, Max sal with name

declare @t table (name varchar(100),dept varchar(100),sal int) insert into @t values ('ambi','dept3',3000) ,('ambi1','dept2',9000) ,('ambi2','dept2',5000) ,('ambi3','dept1',3000) ,('ambi4','dept1',1000) ,('ambi5','dept1',4000) select dept,MAX(sal) sals from @t group by dept select name,a.dept,sal from @t a right join (select dept,MAX(sal) sals from @t group by dept)b on a.dept=b.dept and a.sal=b.sals ;with TBLCTE(EmpID,Ranking) AS ( select dept , Ranking = DENSE_RANK() over (PARTITION BY dept order by NewID()) from @t ) delete from TBLCTE where Ranking > 1 select * from @t SELECT TOP 1 sal FROM ( SELECT DISTINCT TOP 2 sal FROM @t ORDER BY sal DESC) a ORDER BY sal SELECT TOP 5 sal FROM @t ORDER BY sal DESC Select MIN(s.sal) FROM (SELECT TOP 2 sal FROM @t ORDER BY sal DESC)s declare @Emp table (id int, ename varchar(4)) declare @Dept table (dept char(1), salary int, id int) insert into @Emp values (1, 'apv'), (2, 'sug'), (3, 'raj'), (4, 'ram'), (5, 'sam'), (6, 'apv1'), (7, 'sug1'), (8, 'raj1'), (9, 'ram1'), (10, 'sam1') insert into @Dept values ('a', 1000, 1), ('b', 2000, 2), ('c', 3000, 3), ('d', 5000, 4), ('e', 7000, 5), ('a', 20000, 6), ('b', 500, 7), ('c', 5000, 8), ('a', 1000, 9), ('b', 90000, 10) ;with cte as ( select id, salary, dept, rank() over(partition by dept order by salary desc) as rn from @Dept ) select e.ename, e.id, c.salary, c.dept ,rn from cte as c inner join @Emp as e on c.id = e.id order by dept --where rn = 1