Solved

numbering repetaed records 12 123 1 1234 etc sql ms 2005

Posted on 2008-10-16
5
188 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
Comment Utility
This should do:

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

0
 

Author Comment

by:robrodp
Comment Utility
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
Comment Utility
you forgot the comma between email and row_number()
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
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
Comment Utility
Got it....

Sorry
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.​
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now