• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

numbering repetaed records 12 123 1 1234 etc sql ms 2005

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
robrodp
Asked:
robrodp
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
This should do:

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

0
 
robrodpAuthor Commented:
select email row_number() over (partition by email order by email) from icrea

I get this error:

Incorrect syntax near ')'.
0
 
BrandonGalderisiCommented:
you forgot the comma between email and row_number()
0
 
BrandonGalderisiCommented:
and the reason it is giving an error is because it thinks you are trying to alias email as row_number
0
 
robrodpAuthor Commented:
Got it....

Sorry
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now