locked
SELECT records with Maximum ROW_NUMBER RRS feed

  • Question

  • Hello everyone.

    I have a query that selects records and assign a row_number to each selected record based on some logic, and the result is almost like this:

    And I like to select records that have the maximum Row_Number:

    Any idea how I can achieve this?

    Thanks.

     


    http://thebipalace.wordpress.com

     



    • Edited by SaeedB Monday, January 9, 2012 3:45 AM
    Monday, January 9, 2012 3:38 AM

Answers

  • When you assign row_number()  over a column, Can't you reverse the order by column Like DESC if you are using ASC.

    Then use row number =1

    ;WITH CTE
    AS
    (
    SELECT Customer_ID,Store_ID,ROW_NUMBER() OVER(ORDER BY Your_OrderByColumn DESC) as Rnum FROM YOUR_TABLE_NAME 
    )
    Select * from CTE where rnum=1
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Marked as answer by SaeedB Monday, January 9, 2012 3:59 AM
    Monday, January 9, 2012 3:56 AM

All replies

  • When you assign row_number()  over a column, Can't you reverse the order by column Like DESC if you are using ASC.

    Then use row number =1

    ;WITH CTE
    AS
    (
    SELECT Customer_ID,Store_ID,ROW_NUMBER() OVER(ORDER BY Your_OrderByColumn DESC) as Rnum FROM YOUR_TABLE_NAME 
    )
    Select * from CTE where rnum=1
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
    • Marked as answer by SaeedB Monday, January 9, 2012 3:59 AM
    Monday, January 9, 2012 3:56 AM
  • Thanks Vinay.

    I was so stupid that I couldn't work that out.

    Thanks again. :-)


    http://thebipalace.wordpress.com

    Monday, January 9, 2012 3:59 AM
  • Hi,

    That logic you have to use here again.

    You have to go one step ahead.

    Cheers,

    Johni

    Monday, January 9, 2012 8:55 AM