Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Multipurpose Row_Number() Function

Aug 27 2007 9:30AM by Madhivanan   

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

Tags: t-sql, sql_server, row number, row_number,


Madhivanan
3 · 40% · 12968
4
 
 
0
Refreshed
 
 
0
Incorrect



Submit

24  Comments  

  • Thanks madhivanan

    commented on Mar 3 2012 12:15AM
    Guru Samy
    9 · 16% · 5043
  • Hi

    how would i use your statement select rownumber() over(partition by item order by item) as rownumber, * from @t in an update query to set the sequence number field in an existing table

    i tried this update CAIUPDATEAP set TranSequence = (select rownumber() over(partition by trannum order by Trannum) as rownumber, * from CAIUPDATE_AP) but got an error Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    Thanks this is EXACTLY what i was looking for

    Vic

    commented on Sep 15 2012 11:25AM
    vmanfredi
    1116 · 0% · 23
  • vmanfredi, You need to try this

    update T set T.TranSequence =S.TranSequence FROM TranSequence as T inner join 
    (select rownumber() over(partition by trannum order by Trannum) as rownumber, * from CAIUPDATE_AP)  as T
    on S.uniqu_col=T.unique_col
    
    commented on Sep 17 2012 4:59AM
    Madhivanan
    3 · 40% · 12968
  • Hi

    I have another related issue to this your solution to my first question worked perfrctly. I need to write an update statement that looks in table A finds the highest TranSeq number then take that number increase it by one then update table B transeq with that incremented number. So in the example below table B transeq needs to be updated to 3. This need to be dynamic as well, there could be more than one record in table b so I need to take that into account and keep incrementing and uptading table b until the next trannum,

    Any ideas?

    Table A TranNum TranSeq 123 0 123 1

    Table B TranNum TranSeq 123 1

    commented on Sep 27 2012 3:04PM
    vmanfredi
    1116 · 0% · 23
  • vmanfredi, can you post some sample data with expected result?

    commented on Oct 1 2012 3:29AM
    Madhivanan
    3 · 40% · 12968
  • Sure

    this is the data before
    Table A
    TranNum            TranSeq
    123                    0
    123                    1
    456                     100
    456                     101
    Table B
    TranNum              TranSeq
    123                    1
    456                       1
    456                       1   
    here is the result
    
    I need to update the tran seq here in table B to be the next increment from table A taking multiple records for each tran num into account
    Table B
    TranNum               TranSeq
    123                       2
    456                        103
    456                        104
    
    commented on Oct 1 2012 4:17AM
    vmanfredi
    1116 · 0% · 23
  • thanks!!!!

    commented on Oct 1 2012 4:18AM
    vmanfredi
    1116 · 0% · 23
  • See if you get correct result

    select t1.TranNum,t1.sno+t2.sno-1 from
    (
    select TranNum , max(tranSeq)+row_number() over (partition by TranNum order by TranNum) as sno from TableA 
    group by TranNum 
    ) as t1 inner join
    (
    select TranNum , row_number() over (partition by TranNum order by TranNum) as sno from TableB 
    ) as t2
    on t1.TranNum =t2.TranNum
    
    commented on Oct 1 2012 4:49AM
    Madhivanan
    3 · 40% · 12968
  • That worked perfectly. thank so much.I now have another issue here is the sample data.

    How would I return JUST 456 and abc in a query result using tran_seq as the criteria.
    tran_num tran_seq
    123            0
    123            1
    123            2
    456            0
    789            0
    789            1
    abc            0
    
    commented on Oct 10 2012 4:40AM
    vmanfredi
    1116 · 0% · 23
  • If you want to return TranNum that has only 0 as Tran_seq, try this

    select TranNum from
    (
    select t1.TranNum,t1.sno+t2.sno-1 as Sno from
    (
    select TranNum , max(tranSeq)+row_number() over (partition by TranNum order by TranNum) as sno from TableA 
    group by TranNum 
    ) as t1 inner join
    (
    select TranNum , row_number() over (partition by TranNum order by TranNum) as sno from TableB 
    ) as t2
    on t1.TranNum =t2.TranNum
    ) as t2
    group by TranNum
    Having min(sno)=max(Sno) and Min(Sno)=0
    
    commented on Oct 10 2012 5:21AM
    Madhivanan
    3 · 40% · 12968
  • hi thanks for that but I am only using a single table in this senario. The answer you gave before worked perfectly an I thank you for that. I dont need to increment the seq here just return the tran num that has ONLY a tran seq of 0.

    commented on Oct 10 2012 5:58AM
    vmanfredi
    1116 · 0% · 23
  • If it is a single table, use

    select TranNum from table
    Group by TranNum 
    Having min(Tran_num)=max(Tran_num) and min(Tran_num)=0
    
    commented on Oct 10 2012 6:13AM
    Madhivanan
    3 · 40% · 12968
  • i got this error Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value ' FEW' to data type int. Not all the tran nums are numbers. Forgot to mention that

    commented on Oct 10 2012 6:30AM
    vmanfredi
    1116 · 0% · 23
  • It should be tran_seq, try this

    select TranNum from table
    Group by TranNum 
    Having min(Tran_seq)=max(Tran_seq) and min(Tran_seq)=0
    
    commented on Oct 10 2012 6:42AM
    Madhivanan
    3 · 40% · 12968
  • that did it! great thanks so much

    commented on Oct 10 2012 6:51AM
    vmanfredi
    1116 · 0% · 23
  • Hi I have a new wrinkle. I now need this to add account name from the AMTRAN table as well as tran_number.

    So in other words there now can be the same trannum assigned to multiple account names but they could have diffrent transeq. I need to get the max tran_seq by account then by tran num

    Thanks for all your help!!!

    print("select t1.Tran_Num,t1.sno+t2.sno-1 as Tran_Seq from
    

    ( select TranNum , max(TRANSEQUENCE)+rownumber() over (partition by TranNum order by TranNum) as sno from AMTRAN
    group by Tran
    Num ) as t1 inner join ( select docnum , rownumber() over (partition by docnum order by docnum) as sno from CAIUPDATEAR ) as t2 on t1.TranNum =t2.docnum");

    commented on Oct 17 2012 5:25AM
    vmanfredi
    1116 · 0% · 23
  • Is your query working fine?

    commented on Oct 17 2012 6:42AM
    Madhivanan
    3 · 40% · 12968
  • oh yes everything you gave me before works perfectly. This is a new wrinkle where Now there can be the same trannum spread out over several diffrent accounts. So I need to find the max tran seq not only by tran num but now by account. See one account could have 3 tran seqences and an another account can have 10 but all fro the same trannum

    thanks Vic

    commented on Oct 17 2012 6:59AM
    vmanfredi
    1116 · 0% · 23
  • In the Partition by clause you need to include both tran num and account columns

    commented on Oct 18 2012 12:17AM
    Madhivanan
    3 · 40% · 12968
  • Hi again. You have been so helpful I want to thank you again.

    Everything I have tried to over come this issue has not worked so I want to try a diffrent approach. How would I write a trigger on a table that would fire on insert and create the correct tran_seq I need.

    It needs to look at a table called AMTRAN grab the Account and the tran_source then the tran Number then calculate the next seq and insert that new seq with the record I am trying to insert.

    Thanks Vic

    commented on Oct 19 2012 9:55AM
    vmanfredi
    1116 · 0% · 23
Previous 1 | 2 Next

Your Comment


Sign Up or Login to post a comment.

"Multipurpose Row_Number() Function" rated 5 out of 5 by 4 readers
Multipurpose Row_Number() Function , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]