Answer

Oracle SQL: Using the analytic rank function

I have an application that records golf scores in a golf tournament and I'm trying to write SQL to determine the players for 1st, 2nd and 3rd places based on score.

Let's say the table includes:

create table player(player_id number,tournament_id, score number);
create table (player_id number, handicap number);
create tournament (tournament_id number, 1st_place_dollars number(5,2), 2nd_place_dollars number(5,2), 3rd_place_dollars number(5,2));

The number of players could range from 4 to 100 (or more)

The scores could be in any combination, for example:

Player1, 68  wins 1st
Player2, 69  wins 2nd
Player3, 70  wins 3rd
Player4, 72
etc.

The problem is when ties come into play.

example 1
player1 68 wins 1st
player2 69 wins 1/2 of (second + thrid place)
player3 69 wins 1/2 of (second + third place)
player4 70
etc

example 2
player1 68 wins 1st
player2 69 wins 1/3 of (second + thrid place)
player3 69 wins 1/3 of (second + third place)
player4 69 wins 1/3 of (second + third place)
player5 70
etc

example 3
player1 68 wins 1st
player2 69 wins 1/4 of (second + thrid place)
player3 69 wins 1/4 of (second + third place)
player4 69 wins 1/4 of (second + third place)
player5 69 wins 1/4 of (second + third place)
player6 70
etc

example 4  (as you can this could continue for ties)
player1 68 wins 1st
player2 69 wins 1/5 of (second + thrid place)
player3 69 wins 1/5 of (second + third place)
player4 69 wins 1/5 of (second + third place)
player5 69 wins 1/5 of (second + third place)
player6 69 wins 1/5 of (second + third place)
player7 70
etc

example 5 (in this example only for ties for 1st place)
The player with the lowest handicap will win 1st and then the others will win 2nd or split 2nd and 3rd place)
player1 68
player2 68
player3 69
player4 70

I'm trying to put this SQL into a stored procedure. I can do all this using IF, ELSEIF, ELSE or CASE statements but it takes a lot of code.  Is there a simple RANK using greatest and lowest to come up with appropriate ranking?

    Requires Free Membership to View

You're looking for the analytic rank function. Here's a simple example:

create table player(player_id number,tournament_id number, score number);
insert into player values (1,1,68);
insert into player values (2,1,69);
insert into player values (3,1,69);
insert into player values (4,1,69);
insert into player values (5,1,70);
insert into player values (6,1,70);
insert into player values (7,1,71);
insert into player values (8,1,72);
insert into player values (9,1,72);
insert into player values (10,1,73);
insert into player values (11,1,73);
insert into player values (12,1,73);
insert into player values (13,1,74);
insert into player values (14,1,75);
commit;

SQL> select player_id, rank() over (partition by tournament_id order by score)  as place
 2  from player
 3  order by place, player_id ;
PLAYER_ID      PLACE
---------- ----------
        1          1
        2          2
        3          2
        4          2
        5          5
        6          5
        7          7
        8          8
        9          8
       10         10
       11         10
       12         10
       13         13
       14         14

The dense_rank function would not skip places but would allow you to have multiple people in a single place followed by the next place as follows:

SQL> select player_id, dense_rank() over (partition by tournament_id order by score)  as place
 2  from player
 3  order by place, player_id ;
PLAYER_ID      PLACE
---------- ----------
        1          1
        2          2
        3          2
        4          2
        5          3
        6          3
        7          4
        8          5
        9          5
       10          6
       11          6
       12          6
       13          7
       14          8

This was first published in December 2011

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: