CODEWITHSUNDEEP
sqloracle-database
Oskar Selberg
Oskar Selberg

Reputation: 3

ORACLE SQL Ordering issue

This is how my list looks like:

STUDENTID            COURSEID      PLACE
-------------------- -------- ----------
1                    XXX123            1 
2                    YYY123            2 
2                    XXX123            3

The PLACE is a sequence for when the STUDENTID registerd on the COURSEID, so the first to ever register for a course is given the number 1 and so on.

The problem I'm having is that I need to re-arrange the list to look something like this:

STUDENTID            COURSEID      PLACE
-------------------- -------- ----------
1                    XXX123            1 
2                    YYY123            1 
2                    XXX123            2

So PLACE is a number which order the COURSEID. When i delete a row in the table the second person need to be numbered 1, a.s.o.

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can do this using the row_number() function:

select studentid, courseid, row_number() over (partition by courseid order by place) as place
from table t;

Because you want this to be recalculated when there are changes to the data, I would not suggest that you change the underlying data. Just create a view that has this logic:

create view v_courseplace as
    select studentid, courseid, row_number() over (partition by courseid order by place) as place
    from table t;

Unless you have a really large table, performance should not be an issue.

Upvotes: 1

Related Questions