x

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

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.

more ▼

asked Oct 20, 2011 at 06:17 PM in Default

zaingfy gravatar image

zaingfy
13 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest

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.
more ▼

answered Oct 20, 2011 at 10:26 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Thank you. It worked great. We upgraded from 2000 to 2008 recently and I'm learning all the "new" functions.
Oct 21, 2011 at 07:23 PM zaingfy
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x36
x3

asked: Oct 20, 2011 at 06:17 PM

Seen: 935 times

Last Updated: Oct 21, 2011 at 12:22 AM