SQL to determine tee order in a golf application

Question

I am working on a golf app that includes a scorecard. I store every point for each player in the database, and I need to come up with a query to determine the tee order. So, for example, if the players played 3 holes, and the ratings 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 should look like this ...

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

... Thus, players will be ordered with their points compared to the results of their opponents. Does this make sense? Is this possible with a query, or should I write a function to parse a 2d array in code? In this case, I use Java.

My table structure is as follows:

• Players (player ID and player name)
• Rounds (round identifier, course identifier)
• Points (round identifier, player identifier, hole number and rating)

Answer 1

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

Here is my recommendation for implementing the solution without making many requests back:

(1) Assign tee order for the first tee.

(2) For each next tee, pay attention to the previous score and previous order:

(3) Assign a new triple order by going over the previous scores, ordering the highest DESC score and the previous ASC tiy order.

Since you only have a few players per round, it makes sense to do this at the application level. However, if you have a database that supports the window function, then you can easily make a solution only for the database.

I can not resist. Here is the code that will do this with a table for storing orders. You need to scroll once per hole:

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

Initialize it with each player in a certain 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 will need to call it once for each hole, minus one.

Then receive your order as:

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

Answer 2

So, you will need to summarize the scores for each hole to the current hole for each player, and then sort the list of player IDs by total points.

Something like this should work:

 SELECT SUM(score) AS score, player_id FROM Scores WHERE round_id=$current_round AND hole_number<$current_hole GROUP BY player_id ORDER BY score; 

The result is a table of results from two columns (player_id and score) with as many rows as there are players, first putting the player with the highest score.

Answer 3

This query returns the highest hole in which the player had the lowest score and orders:

 SELECT * FROM Players p ORDER BY (SELECT MAX(s1.hole_number) FROM Scores s1 LEFT JOIN Scores s2 ON s2.round_id = s1.round_id AND s1.hole_number = s2.hole_number AND s2.score < s1.score WHERE s1.player_id = p.player_id AND s1.round_id = 1 AND s2.round_id IS NULL) DESC