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?
PortletPaulConnect With a Mentor freelancerCommented:
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()
searchsanjaysharmaAuthor Commented:
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.