Post reply

Query

  • July 5, 2016 at 5:34 am

    #1888421

    Hi,

    Please try using following code-

    DECLARE @T TABLE (ID INT, NAME VARCHAR(1))

    INSERT @T VALUES (1000,'A'),(990,'C'),(980,'A'),(970,'A'),(960,'B'),(950,'C'),(930,'A')

    SELECT * FROM(SELECT ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY ID DESC)ROWNUM,ID,NAME FROM @T )T

    WHERE T.ROWNUM<=3

    Regards,

    Anand

  • July 5, 2016 at 5:42 am

    #1888425

    shirolkar.anand (7/5/2016)

    Hi,

    Please try using following code-

    DECLARE @T TABLE (ID INT, NAME VARCHAR(1))

    INSERT @T VALUES (1000,'A'),(990,'C'),(980,'A'),(970,'A'),(960,'B'),(950,'C'),(930,'A')

    SELECT * FROM(SELECT ROW_NUMBER() OVER( PARTITION BY NAME ORDER BY ID DESC)ROWNUM,ID,NAME FROM @T )T

    WHERE T.ROWNUM<=3

    Regards,

    Anand

    This doesn't work, the names are in incorrect order.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 5, 2016 at 6:41 am

    #1888440

    @anand-2,

    The OP already got the ID's in order, his requirement is to get the names in order based on highest ID which is the tricky part.

    Together We've already solved it, kindly refer to the solution.

Viewing 3 posts - 16 through 17 (of 17 total)

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