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/