numbering repetaed records 12 123 1 1234 etc sql ms 2005

Posted on 2008-10-16
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

John 1
John 2
Mike 1
Sam  1
Sam 2
Sam 3
Question by:robrodp
  • 3
  • 2
LVL 39

Accepted Solution

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

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


Author Comment

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

I get this error:

Incorrect syntax near ')'.
LVL 39

Expert Comment

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

Expert Comment

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

Author Comment

ID: 22736910
Got it....


Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL - Rotating Values in SQL 9 65
Getting same value for every field in SQL 2 44
Access 2016 - query 23 59
Help with query 3 23
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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