Q

Oracle SQL: Using the analytic rank function

Expert Karen Morton explains how to use a simple analytic rank function to sort through a group of golf scores.

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?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close