HELP WITH SELECT STATEMENT 1

[Freemo]

Hi,

I am running a select query that identifies added riders on our system. It produces the fields policynumber, name and dob. When there is more than one added rider i get 2 rows which have the same policynumber and different name and dob. What i would like to do is assign a number to each row so the top row would be 1 and the next 2 and if present the next would be 3 for the multiple rows under the same policy number.

Hope that makes sense.

Many thanks

 

[ArunPrithviraj]

Hi Freemo,

Use the ROW_NUMBER() OVER (ORDER BY column DESC)
in case u r using SQL Server 2005

Thanks,
AP

 

[Freemo]

Thanks ArunPrithviraj for the quick response.

I have tried that it does not quite come up with the result i am looking for. That adds the row number so the records are numbered 1 to 118 (the select query returns 118 rows). I want the number to be reset everytime the policy number changes.

 

[Freemo]

Figured it - needed to use the PARTITION BY as well as ORDER BY clause.

Ta