Row Number With Repeated Numbers

• rootfixxxer

  SSCrazy

  Points: 2918

  More actions

  July 9, 2016 at 9:11 am

  #328109

  Hello

  The topic name it isn't the best, but it's what i need.

  So i have a list of times from a race, i need to order the athletes to show, the row number works just fine until last week.

  But now the requirements changed, now if the athletes have the same time, they need to have the same number so i have a row count with repeated numbers.

  Sample:

  DECLARE @TestTable TABLE (Athelete NVARCHAR(100),SPLIT BIGINT)

  INSERT INTO @TestTable

  SELECT 'A',100

  UNION ALL

  SELECT 'B',50

  UNION ALL

  SELECT 'C',110

  UNION ALL

  SELECT 'D',50

  SELECT [POS] = ROW_NUMBER() OVER (ORDER BY Split),Athelete,Split

  FROM @TestTable

  So the expected table, should be POS 1,1,2,3...

  Thanks

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 9, 2016 at 9:25 am

  #1889301

  rootfixxxer (7/9/2016)

  ---

  Hello

  The topic name it isn't the best, but it's what i need.

  So i have a list of times from a race, i need to order the athletes to show, the row number works just fine until last week.

  But now the requirements changed, now if the athletes have the same time, they need to have the same number so i have a row count with repeated numbers.

  Sample:

  DECLARE @TestTable TABLE (Athelete NVARCHAR(100),SPLIT BIGINT)

  INSERT INTO @TestTable

  SELECT 'A',100

  UNION ALL

  SELECT 'B',50

  UNION ALL

  SELECT 'C',110

  UNION ALL

  SELECT 'D',50

  SELECT [POS] = ROW_NUMBER() OVER (ORDER BY Split),Athelete,Split

  FROM @TestTable

  So the expected table, should be POS 1,1,2,3...

  Thanks

  try this

  SELECT [POS] = DENSE_RANK() OVER (ORDER BY Split),Athelete,Split

  FROM @TestTable

  also consider this for other scenarios

  SELECT [POS] = RANK() OVER (ORDER BY Split),Athelete,Split

  FROM @TestTable

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• rootfixxxer

  SSCrazy

  Points: 2918

  More actions

  July 9, 2016 at 9:48 am

  #1889303

  Thanks

  Came from the 2005 version, and didnt know this functions...

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  July 9, 2016 at 10:27 am

  #1889307

  rootfixxxer (7/9/2016)

  ---

  Thanks

  Came from the 2005 version, and didnt know this functions...

  "every day is a school day " 🙂

  glad to be of help

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day