Ranking using 2 columns in T-SQL 2012

  • 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

  • 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/

  • 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

  • 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.

  • 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!

  • 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/

  • 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.

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

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