Solved

numbering repetaed records 12 123 1 1234 etc sql ms 2005

Posted on 2008-10-16
5
198 Views
Last Modified: 2010-03-19
I have a table with n records (1 field each) I nee an sqk query that will order them by field and add a consecutive number to each repeted record starting from 1 then 2 then 3 ..until the next different record(field) is reached abd star againnwith 1,2 123,1, etc

Ex
John 1
John 2
Mike 1
Sam  1
Sam 2
Sam 3
0
Comment
Question by:robrodp
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 22736714
This should do:

select YourField, row_number() over (partition by YourField order by yourField) from YourTable

0
 

Author Comment

by:robrodp
ID: 22736801
select email row_number() over (partition by email order by email) from icrea

I get this error:

Incorrect syntax near ')'.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22736861
you forgot the comma between email and row_number()
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22736868
and the reason it is giving an error is because it thinks you are trying to alias email as row_number
0
 

Author Comment

by:robrodp
ID: 22736910
Got it....

Sorry
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question