Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

HELP WITH SELECT STATEMENT 1

Status
Not open for further replies.

Freemo

MIS
Oct 25, 2005
71
GB
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
 
Hi Freemo,

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

Thanks,
AP
 
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.

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

Ta
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top