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.
Best Answer
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.
Thank you. It worked great. We upgraded from 2000 to 2008 recently and I'm learning all the "new" functions.
