CODEWITHSUNDEEP
sqloracle-database
Radi
Radi

Reputation: 6584

Ranking each group of table rows

I have a table with this structure :

ID   NAME   RANK
10    A      1
11    A      2
12    A      3
13    A      4
14    B      1
15    B      2

This table is huge and around 500 rows are inserted to it every minute. To maintain ordering for each group, by name, we are using a before insert trigger like the following:

begin

    SELECT NVL(MAX(RANK+1),1) INTO RANK FROM tablename
    WHERE NAME=:NEW.NAME;

end;

This works well but sometimes it returns incorrect values e.g (14,8,11,4,5) instead of (1,2,3,4,5). We investigated our code so we didn't update this column.

What could be the problem? If this method for ranking is wrong, what is the best method to do that?

Upvotes: 0

Views: 101

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

As stated in my comment, I see no reason for values being higher than expected. So I cannot actually answer your original question.

However, I suggest you use a sequence instead, as also mentioned in my comments above. A sequence is guaranteed to work with concurrent access, which your approach is not. To have at last consecutive values, you would use an aggregate function for that:

select name, row_number() over (partition by name order by seq_no) as rank_no
from tablename;

You can create a view, hiding seq_no and only showing rank_no. Thus your client gets what they want to see.

Upvotes: 1

Related Questions