Getting Row numbers for each people group

• ntreelevel

  Say Hey Kid

  Points: 663

  More actions

  February 18, 2015 at 1:29 pm

  #295428

  Hi,

  I have a set of rows in a table like for example

  Client ID Client Name Date Score

  1 Smith 12/31/2014 25

  1 Smith 10/15/2014 45

  2 John 08/11/2014 55

  2 John 06/18/2014 15

  3 Rose 04/15/2014 12

  4 Mike 07/23/2014 28

  5 Mary 01/5/2014 56

  6 Lisa 08/1/2014 54

  6 Lisa 05/10/2014 34

  Now I want to use Row Number function or any way where I can get the result as below

  Client ID Client Name Date Score RowNo

  1 Smith 12/31/2014 25 1

  1 Smith 10/15/2014 45 2

  2 John 08/11/2014 55 1

  2 John 06/18/2014 15 2

  3 Rose 04/15/2014 12 1

  4 Mike 07/23/2014 28 1

  5 Mary 01/5/2014 56 1

  6 Lisa 08/1/2014 54 1

  6 Lisa 05/10/2014 34 2

  Thanks In Advance!

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  February 18, 2015 at 1:48 pm

  #1778467

  What is your logic when there are ties?

  For example, you have two records for Smith. It looks like the row number is assigned from the lowest score to the highest score.

  But with John it is the other way around.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 18, 2015 at 2:13 pm

  #1778472

  assuming Koens assumption on Score,

  :

  ;WITH MyCTE([ClientID],[ClientName],[Date],[Score])

  AS

  (

  SELECT '1','Smith','12/31/2014','25' UNION ALL

  SELECT '1','Smith','10/15/2014','45' UNION ALL

  SELECT '2','John','08/11/2014','55' UNION ALL

  SELECT '2','John','06/18/2014','15' UNION ALL

  SELECT '3','Rose','04/15/2014','12' UNION ALL

  SELECT '4','Mike','07/23/2014','28' UNION ALL

  SELECT '5','Mary','01/5/2014','56' UNION ALL

  SELECT '6','Lisa','08/1/2014','54' UNION ALL

  SELECT '6','Lisa','05/10/2014','34'

  )

  SELECT *, row_number() over (partition by [ClientID] ORDER BY [Score]) As RowNo FROM MyCTE;

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• ntreelevel

  Say Hey Kid

  Points: 663

  More actions

  February 19, 2015 at 6:02 am

  #1778613

  This worked perfect. Thanks A lOT!!!:-)