How to generate id with increment order on change of every course

I have the following table custuds with following structure
create table custuds(course varchar(200),sname varchar(200),fname varchar(200),
mname varchar(200),id bigint)

The sample data is attached.

The table is sorted on the basis of course, sname.
The id should be generated in such a way that at change of every course, the id should begin from 1
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
ID of a table would typically be immutable (does not get changed) and simply "the next number" with NO algorithm based on other fields or sort sequence.

i.e. ID should simply provide a unique number for each row (full stop)

You can then always use a query to dynamically supply what you are asking for

select course,sname , fname , mname
, row_number() over (partition by course order by sname, fname , mname ) as rn
from custuds
where <<whatever>

see: row_number()

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
searchsanjaysharmaAuthor Commented:
PortletPaulEE Topic AdvisorCommented:
searchsanjaysharma, I am very pleased you have an answer
- but would ask why you have given it C as a grading?

Did I fail to explain something?
I try to make my responses as complete as I can, was it really so bad it deserved "C"?
(C is considered a "bad answer")

What grade should I award?
C should only be given for an incomplete solution that does not fully address or answer the question. A C grade should be awarded only after the asker has replied to all expert comments, provided all requested information, tried all suggested solutions, given the experts ample time to reply, and received clarification about the answer given. The asker must justify giving a C grade and give the experts an opportunity to improve it.
How can I improve on the answer?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.