Nov 25, 2011
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
Subscribe to:
Posts (Atom)