SQL to Determine Tee Order in Golf Application

Question

I am working on a golf application that includes a scorecard system. I am storing each score for each player in the database and I need to come up with a query to determine tee order. So for example if the players have played 3 holes and the scores look like this...

Player    1  2  3
--------- -  -  -
Player 1: 3, 4, 3
Player 2: 2, 3, 3
Player 3: 2, 4, 3

... Then the order needs to look like this...

1.) Player 2
2.) Player 3
3.) Player 1

... So the players will be ordered by their scores compared to their opponents scores. Does that make sense? Is this even possible with a query, or should I write a function to parse a 2d array in code? I am using Java in that case.

My table structure looks like this:

• Players (player id, and player name)
• Rounds (round id, course id)
• Scores (round id, player id, hole number, and score)

Answer 1

I can see a solution that uses windows functions row_number() and an additional column in the database for the ordering at each level (or a recursive CTE in SQL Server). However, SQLite does not support this.

Here is my recommendation on implementing the solution without doing a lot of querying backwards:

(1) Assign the tee order for the first tee.

(2) For each next tee, look at the previous score and the previous tee order:

(3) Assign the new tee order by looping through the previous scores by ordering by highest score DESC and previous tee order ASC.

Because you only have a few players per round, it is reasonable to do this in the app layer. However, if you had a database that supported window function, then you could more easily do a database only solution.

I can't resist. Here some code that will do this with a table to store the orders. You need to loop through, once per hole:

create table ThisOrder (
    ThisOrderId int primary key autoincrement,
    RoundId int,
    Hole int,
    PlayerId int
)

Initialize it with each player in some order.

Then, insert new rows into the table for each hole:

insert into ThisOrder(RoundId, HoleId, PlayerId)
    select s.RoundId, s.Hole+1, s.PlayerId
    from Scores s join
         ThisOrder to
         on s.PlayerId = to.PlayerId and
            s.RoundId = to.RoundId and
            s.Hole = to.Hole
    order by s.Score DESC, to.Order ASC

You'll need to call this once for each hole, minus one.

Then get your ordering as:

 select *
 from ThisOrder
 where roundid = <roundid> and hole = <thehole>
 order by ThisOrderId