A Usage of Row_Number Function (T-SQL)


An example to use Row_Number function to solve an complex problem on MSDN:
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/84a30edf-67a9-4598-8aef-cc6594135113



create table #temp
(rownum int,
id int,
r_id varchar(10),
ord int,
w_no int,
r_no varchar(10),
dt_Frm datetime,
tr_time datetime
)
insert into #temp values ('2','101','101||3','3','37','925','2012-09-27','1900-01-01 14:30:00.000')
insert into #temp values ('4','101','101||8','8','55','978','2012-10-18','1900-01-01 08:51:00.000')
insert into #temp values ('5','101','101||9','9','55','976','2012-10-18','1900-01-01 14:44:00.000')
insert into #temp values ('6','101','101||12','12','42','1425','2013-01-01','1900-01-01 12:12:00.000')
insert into #temp values ('8','101','101||17','17','55','976','2013-01-07','1900-01-01 08:43:00.000')
insert into #temp values ('9','101','101||20','20','65','1876','2013-01-09','1900-01-01 09:34:00.000')
insert into #temp values ('2','102','102||3','3','37','1451','2012-08-18 00:00:00.000','1900-01-01 14:51:00.000')
insert into #temp values ('4','102','102||8','8','37','393','2012-08-30 00:00:00.000','1900-01-01 13:05:00.000')
insert into #temp values ('5','102','102||9','9','37','1451','2012-10-09 00:00:00.000','1900-01-01 22:36:00.000')
insert into #temp values ('6','102','102||12','12','42','276','2013-01-16 00:00:00.000','1900-01-01 00:18:00.000')
insert into #temp values ('8','102','102||17','17','42','276','2013-01-21 00:00:00.000','1900-01-01 01:03:00.000')
insert into #temp values ('8','102','102||17','17','42','1147','2013-01-26 00:00:00.000','1900-01-01 16:36:00.000')
insert into #temp values ('9','102','102||20','20','65','1876','2013-02-07 00:00:00.000','1900-01-01 11:00:00.000')

 ;with mycte as
 (
 select *,  row_number() over( order by  dt_Frm DESC) - row_number() over(partition by w_no order by dt_Frm DESC)  deltaRn from #temp
 
 )

 ,mycte1 as
 (
 Select *,  row_number() over(partition by id,w_no, deltaRn  order by id, dt_Frm DESC, r_no) rn from mycte)

 Select * from mycte1 WHERE rn=1
 order by id, rownum

/*********************************************************/

create table #result_temp
(rownum int,
id int,
r_id varchar(10),
ord int,
w_no int,
r_no varchar(10),
dt_Frm datetime,
tr_time datetime
)
insert into #result_temp values ('2','101','101||3','3','37','925','2012-09-27','1900-01-01 14:30:00.000')
insert into #result_temp values ('5','101','101||9','9','55','976','2012-10-18','1900-01-01 14:44:00.000')
insert into #result_temp values ('6','101','101||12','12','42','1425','2013-01-01','1900-01-01 12:12:00.000')
insert into #result_temp values ('8','101','101||17','17','55','976','2013-01-07','1900-01-01 08:43:00.000')
insert into #result_temp values ('9','101','101||20','20','65','1876','2013-01-09','1900-01-01 09:34:00.000')
insert into #result_temp values ('5','102','102||9','9','37','1451','2012-10-09 00:00:00.000','1900-01-01 22:36:00.000')
insert into #result_temp values ('8','102','102||17','17','42','1147','2013-01-26 00:00:00.000','1900-01-01 16:36:00.000')
insert into #result_temp values ('9','102','102||20','20','65','1876','2013-02-07 00:00:00.000','1900-01-01 11:00:00.000')


select * from #result_temp

drop table #temp
drop table #result_temp



Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s