Query

• shirolkar.anand

  Valued Member

  Points: 59

  More actions

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  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

• durga.palepu

  Hall of Fame

  Points: 3530

  More actions

  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.