Increment numbers (1-10) based on order of age (1- rest of members then repeat)

Question

In my database I have 3 fields that I'm working from

MasterID  Name     Age

2301  John Smith    55
2301  Jane Smith    51
2301  Joe Smith     23
2301  Janet Smith   21
2502  Steve Johnson 29
2502  Steph Johnson 27

Now someone asked to add on the report "order of Dependents based on age" which I did, but now they want a Dep# (ordered by age) starting from the number 1. So they want it to look like this.

  MasterID  Name     Age  Dep#

2301  John Smith     55    1
2301  Jane Smith     51    2
2301  Joe Smith      23    3
2301  Janet Smith    21    4
2502  Steve Johnson  29    1
2502  Steph Johnson  27    2

Does anyone have an example on how to do this?

Thank you in advance.

Answer 1

0

So on SQL Server 2005 and above you can use a ROW_NUMBER() function for this with partitioning by MasterID and sorting the data in descending order by Age.

SELECT
    MasterID,
    Name,
    Age,
    ROW_NUMBER() OVER(PARTITION BY MasterID ORDER BY Age DESC) AS [Dep#]
FROM DataTable

This query should provide exactly your results.

answered Oct 20 '11 at 10:26 PM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

Thank you. It worked great. We upgraded from 2000 to 2008 recently and I'm learning all the "new" functions.

Oct 21 '11 at 07:23 PM zaingfy

(comments are locked)

Increment numbers (1-10) based on order of age (1- rest of members then repeat)

Follow this question

Related Questions