Ranking using 2 columns in T-SQL 2012

Question

Ranking using 2 columns in T-SQL 2012

chrismic999

SSC Enthusiast

Points: 195
More actions
May 27, 2014 at 9:18 am

#291731

I have a table where basically I want to identify the first Invoice placed by an account.
In the file I have 2 columns AccountName and Invoice_Date which I order by and then want to update a Rank_Order Column to state the 1st entry and then extract this for something else. basically I want to know when the company placed its very first order !!
Here's a sample of the data I would like to get out
Can someone help me with the SQL - This is pretty urgent too !!! Thanks
AccountNameINVOICE_DATERank Order
A01/08/20131
A01/09/20132
A01/10/20133
B01/08/20131
B01/08/20131
B03/01/20142
B01/02/20143
B03/02/20144
C01/10/20121
C04/10/20133
C10/04/20132

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

chrismic999 (5/27/2014)
I have a table where basically I want to identify the first Invoice placed by an account.
In the file I have 2 columns AccountName and Invoice_Date which I order by and then want to update a Rank_Order Column to state the 1st entry and then extract this for something else. basically I want to know when the company placed its very first order !!
Here's a sample of the data I would like to get out
Can someone help me with the SQL - This is pretty urgent too !!! Thanks
AccountNameINVOICE_DATERank Order
A01/08/20131
A01/09/20132
A01/10/20133
B01/08/20131
B01/08/20131
B03/01/20142
B01/02/20143
B03/02/20144
C01/10/20121
C04/10/20133
C10/04/20132

Using ROW_NUMBER with PARTITION is how I would do this.

Since you didn't provide ddl this example is untested.

ROW_NUMBER() OVER(PARTITION BY AccountName Order by INVOICE_DATE) as RankOrder

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

enmadrid76 SSC Journeyman Points: 91 More actions · Answer 2

This should do the trick:

select

q.AccountName

,q.INVOICE_DATE as FirstOrderDate

from (

select

AccountName

,INVOICE_DATE

,ROW_NUMBER() OVER(PARTITION BY AccountName order by date desc) as Rank_Order

from table_name

) as q

where q.Rank_Order = 1

order by q.AccountName

David Burrows SSC Guru Points: 65143 More actions · Answer 3

Why not use MIN

SELECT AccountName,MIN(INVOICE_DATE) AS [INVOICE_DATE]

FROM

GROUP BY AccountName

Far away is close at hand in the images of elsewhere.
Anon.

chrismic999 SSC Enthusiast Points: 195 More actions · Answer 4

This seems to work

SELECT accountname,invoice_date, ROW_NUMBER()

OVER(PARTITION BY AccountName Order by INVOICE_DATE) as RankOrder from dbo.sagesales

where AccountName is not null

Insert help from above

But now I want to update a field called "Place" (int) in dbo_sagesales with the result ie the ranked order

thanks

alternatively I would like to extract only those that have a ROW_Number as 1?

I tried where AccountName is not null and Rankorder =1 but it didnt like it in the above query!

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

chrismic999 (5/27/2014)
This seems to work
SELECT accountname,invoice_date, ROW_NUMBER()
OVER(PARTITION BY AccountName Order by INVOICE_DATE) as RankOrder from dbo.sagesales
where AccountName is not null
Insert help from above
But now I want to update a field called "Place" (int) in dbo_sagesales with the result ie the ranked order
thanks
alternatively I would like to extract only those that have a ROW_Number as 1?
I tried where AccountName is not null and Rankorder =1 but it didnt like it in the above query!

You can't reference windowing functions in the where clause. If you only want where the first one then I would use MIN like suggested above. No need to go to all the trouble to number all the rows just to throw most of them away anyway. Look at the code David posted. It is exactly what you need here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Burrows SSC Guru Points: 65143 More actions · Answer 6

Also if your data contains duplicate invoice dates as shown in the result set in your first post then you will need to use DENSE_RANK not ROW_NUMBER.

Plus your two requirements in your last post are not compatible.

Do you want to update all records with the rank or only update the first date?

Is there a unique key on the table?

Far away is close at hand in the images of elsewhere.
Anon.