Multipurpose Row_Number() Function
One of the features available in SQL Server 2005 is Row_Number() function. It is not only used to generate row number for each row but also used for other purposes as well. I breifly explain how it can be used for various purposes
Consider the following data
Declare @t table(item varchar(100), price float)
insert into @t
select 'item1', 20000 union all
select 'item1', 20000 union all
select 'item1', 20700 union all
select 'item2', 57600 union all
select 'item2', 80120 union all
select 'item3', 89760 union all
select 'item3', 87680 union all
select 'item4', 87680 union all
select 'item4', 43220 union all
select 'item4', 43220
(1)
Generate Serial No or replicate identity column
select row_number() over(order by item) as row_number, * from @t
--Result
row_number item price
-------------------- ---------------------- ------------------------------------------
1 item1 20000.0
2 item1 20000.0
3 item1 20700.0
4 item2 57600.0
5 item2 80120.0
6 item3 89760.0
7 item3 87680.0
8 item4 87680.0
9 item4 43220.0
10 item4 43220.0
(2)
Generate Serial No and reset in each group
select row_number() over(partition by item order by item) as row_number, * from @t
--Result
row_number item price
-------------------- ---------------------- ----------------------------------------
1 item1 20000.0
2 item1 20000.0
3 item1 20700.0
1 item2 57600.0
2 item2 80120.0
1 item3 89760.0
2 item3 87680.0
1 item4 87680.0
2 item4 43220.0
3 item4 43220.0
(3)
Select top N data for each group
Select * from
(
select row_number() over(partition by item order by item) as row_number, * from @t
) T
where row_number<=N
where N is a positive integer value
--Result(when N=2)
row_number item price
-------------------- ---------------------- ------------------------------------------
1 item1 20000.0
2 item1 20000.0
1 item2 57600.0
2 item2 80120.0
1 item3 89760.0
2 item3 87680.0
1 item4 87680.0
2 item4 43220.0
(4)
Pagination
Select item,price from
(
select row_number() over(order by item) as row_number, * from @t
) T
where row_number between 1 and 5
--Result
item price
------------------------- ------------------------------------------
item1 20000.0
item1 20000.0
item1 20700.0
item2 57600.0
item2 80120.0
(5)
Find Nth Maximum value
Select price from
(
select row_number() over(order by price desc) as row_number, price from (select distinct price from @t) d
) T
where row_number=2
--Result(where N=2)
price
------------------------------------------
87680.0
(6)
Delete duplicates
delete T from
(
select row_number() over(order by item) as row_number, * from @t
) T
where row_number not in
(
select min(row_number) from
(select row_number() over(order by item) as row_number, * from @t) T
group by item
)
select * from @t
--Result
item price
------------------------- ------------------------------------------
item1 20000.0
item2 57600.0
item3 89760.0
item4 87680.0