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=N

--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
)

or

delete T from
(
select row_number() over(partition by item order by item) as row_number, * from @t
) T
where row_number>1

select * from @t

--Result

item                         price

------------------------- ------------------------------------------

item1                       20000.0
item2                       57600.0
item3                       89760.0
item4                       87680.0

Published 27 August 2007 16:23 by Madhivanan

Comments

# re: Multipurpose Row_Number() Function

27 December 2007 08:33 by Ketan

Hi,

I want to crete a table in my database which contains a primary key with autoincrement  having alphanumeric.

e.g. A00001

      A00002

      A00003

      A00004

can you help me to find out the solution. I am using MS ACCESS and Java. Please send me the solution to my id ketan18jan@gmail.com

# re: Multipurpose Row_Number() Function

27 December 2007 08:41 by Madhivanan

Hi Ketan,

Refer this

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

# Row_number() Function with no specific order

29 November 2008 10:12 by Madhivanan

Row_number() function is used to generate row number to rows based on the column which is ordered What

# Row_number() Function with no specific order

29 November 2008 10:44 by SQL Server Transact-SQL (SSQA.net)

Row_number() function is used to generate row number to rows based on the column which is ordered What

# find second entry date for each user | keyongtech

Pingback from  find second entry date for each user | keyongtech