Post reply

Getting Row numbers for each people group

  • 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!

  • 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

  • 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!

  • February 19, 2015 at 6:02 am

    #1778613

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

Viewing 4 posts - 1 through 3 (of 3 total)

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