Recursive Subquery

Question

I am trying to use this recursive SQL function, but I can’t get it to do what I want, I don’t even close it. I coded the logic in the expanded loop, asking if it could be converted to a single recursive SQL query, and not to the table update style that I used.

http://sqlfiddle.com/#!4/b7217/1

There must be six players in the ranking. They have id, group id, rating and rank.

Initial state

+----+--------+-------+--------+ | id | grp_id | score | rank | +----+--------+-------+--------+ | 1 | 1 | 100 | (null) | | 2 | 1 | 90 | (null) | | 3 | 1 | 70 | (null) | | 4 | 2 | 95 | (null) | | 5 | 2 | 70 | (null) | | 6 | 2 | 60 | (null) | +----+--------+-------+--------+ 

I want to take the person with the highest initial score and give him a rank of 1. Then I apply 10 bonus points to the account of everyone who has the same group identifier. Take the next maximum, assign rank 2, distribute bonus points and so on until there are no players left.

The user identifier breaks the connection.

Bonus points change the rating. id = 4 initially, apparently, ranks second with 95, behind the leader with 100, but with a bonus of 10 points, id = 2 moves up and takes a place.

Final state

 +-----+---------+--------+------+ | ID | GRP_ID | SCORE | RANK | +-----+---------+--------+------+ | 1 | 1 | 100 | 1 | | 2 | 1 | 100 | 2 | | 4 | 2 | 95 | 3 | | 3 | 1 | 90 | 4 | | 5 | 2 | 80 | 5 | | 6 | 2 | 80 | 6 | +-----+---------+--------+------+ 

Answer 1

This is pretty late, but I'm not sure if this can be done with a recursive CTE. However, I came up with a solution using the MODEL clause:

 WITH SAMPLE (ID,GRP_ID,SCORE,RANK) AS ( SELECT 1,1,100,NULL FROM DUAL UNION SELECT 2,1,90,NULL FROM DUAL UNION SELECT 3,1,70,NULL FROM DUAL UNION SELECT 4,2,95,NULL FROM DUAL UNION SELECT 5,2,70,NULL FROM DUAL UNION SELECT 6,2,60,NULL FROM DUAL) SELECT ID,GRP_ID,SCORE,RANK FROM SAMPLE MODEL DIMENSION BY (ID,GRP_ID) MEASURES (SCORE,0 RANK,0 LAST_RANKED_GRP,0 ITEM_COUNT,0 HAS_RANK) RULES ITERATE (1000) UNTIL (ITERATION_NUMBER = ITEM_COUNT[1,1]) --ITERATE ONCE FOR EACH ITEM TO BE RANKED ( RANK[ANY,ANY] = CASE WHEN SCORE[CV(),CV()] = MAX(SCORE) OVER (PARTITION BY HAS_RANK) THEN RANK() OVER (ORDER BY SCORE DESC,ID) ELSE RANK[CV(),CV()] END, --IF THE CURRENT ITEM SCORE IS EQUAL TO THE MAX SCORE OF UNRANKED, ASSIGN A RANK LAST_RANKED_GRP[ANY,ANY] = FIRST_VALUE(GRP_ID) OVER (ORDER BY RANK DESC), SCORE[ANY,ANY] = CASE WHEN RANK[CV(),CV()] = 0 AND CV(GRP_ID) = LAST_RANKED_GRP[CV(),CV()] THEN SCORE[CV(),CV()]+10 ELSE SCORE[CV(),CV()] END, ITEM_COUNT[ANY,ANY] = COUNT(*) OVER (), HAS_RANK[ANY,ANY] = CASE WHEN RANK[CV(),CV()] <> 0 THEN 1 ELSE 0 END --TO SEPARATE RANKED/UNRANKED ITEMS ) ORDER BY RANK; 

This is not very pretty, and I suspect there is a better way to do this, but it gives the expected result.

Cautions:

You need to increase the iteration counter if you have more than this number of lines.

This makes a complete re-ranking based on the score after each iteration. Therefore, if we took your sample data, but changed the initial score from 2 to 95, not 90: after ranking point 1 and providing a 10-point bonus to point 2, now it has a rating of 105. Thus, we rate it as 1 1st and move point 1 to the second. You must make several modifications if this is not the desired behavior.