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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation