Hi,
Pls help me , how to add Rank column (based on salary column and by using dense_rank function) to existing (main) database.
Regards
ajay
; with cte as (
select
101 as Eid
,'Ajay Pal' as Ename
,100000 as Sal
Union all
select
102 as Eid
,'David' as Ename
,80000 as Sal
Union all
select
103 as Eid
,'Jane' as Ename
,75000 as Sal
Union all
select
104 as Eid
,'Rock' as Ename
,90000 as Sal
)
select *,
DENSE_RANK() over (order by sal desc) as [Rank]
from cte
order by Eid
Hi,
Thank you for reply
Table was already created. Now I need to add a Rank column to the existing table. Pls write a query using ALTER & Dense_Rank command.
Thank you in advance.
Regards
SP.Ajay Pal
; with cte as (
select
101 as Eid
,'Ajay Pal' as Ename
,100000 as Sal
Union all
select
102 as Eid
,'David' as Ename
,80000 as Sal
Union all
select
103 as Eid
,'Jane' as Ename
,75000 as Sal
Union all
select
104 as Eid
,'Rock' as Ename
,90000 as Sal
)
select
*
into #test
from cte
order by Eid
alter table #test
add [Rank] int
update a
set a.[Rank] = b.[Rank1]
from #test a
Left join (
select *,
DENSE_RANK() over (order by sal desc) as [Rank1]
from #test
) b on a.Eid = b.Eid and a.Ename = b.Ename and a.Sal = b.Sal
select * from #test