Select lowest number in a column
Posted: Wed Feb 09, 2011 9:36 am
Hello,
I have a script that creates a "Skins" report for a golf league. A "Skin" occurs when a player has the lowest score on a hole.
I have a MySQL DB Server version: 5.1.52
I collect 9 scores from each player with the following php query:
This query returns all the info needed except the low score on a hole if there is one. Each row contains player info, course info and 9 scores from each player. The 9 scores come from the "stats_h_h" table in query.
In order to get the low score on each hole, the first score from a player has to be compared to the first score from every other player, then the second compared to the second score from every other player and so on.
My question is.. Can I get the db to tell me if there is a low score on a hole or do I have to do that with PHP after collecting the data?
[text]Here's an example of hole by hole data, the 3 on the first hole and 4 on the 6th hole are skins:
Holes----------------1 --2 --3 --4 --5 --6 --7 --8 --9
Player1--------------6 --5-- 5 --6 --7-- 7 --6 --5-- 5
Player2--------------3 --4 --5 --6-- 5 --5 --6 --7 --6
Player3--------------9--12 --5 --5 --6 --5 --5 --6 --6
Player4--------------5 --6 --7 --6 --7 --6 --5 --6 --7
Player5--------------4 --4 --5 --5 --5 --4 --5 --5 --5
Player6--------------6 --5 --5 --6 --6 --5 --5 --5 --5 [/text]
Thanks for your help
I have a script that creates a "Skins" report for a golf league. A "Skin" occurs when a player has the lowest score on a hole.
I have a MySQL DB Server version: 5.1.52
I collect 9 scores from each player with the following php query:
Code: Select all
$sql = "
SELECT stats_h_h.*,
member.memNum, member.firstName, member.lastName,
stats.playDate, stats.score, stats.adjScr,
course.courseName, course.holes, course.track,
course_hcp.*,
course_par.*
FROM stats_h_h
LEFT JOIN stats ON stats.statNum = stats_h_h.statNum
LEFT JOIN member ON member.memNum = stats.memNum
LEFT JOIN course ON course.courseNum = stats_h_h.courseNum
LEFT JOIN course_hcp ON course_hcp.courseNum = stats_h_h.courseNum
LEFT JOIN course_par ON course_par.courseNum = stats_h_h.courseNum
WHERE stats.memNum in(SELECT memNum FROM member WHERE leagueName = 'Putters')
AND stats.playDate = '2011-02-08'
AND stats_h_h.courseNum = '26';";In order to get the low score on each hole, the first score from a player has to be compared to the first score from every other player, then the second compared to the second score from every other player and so on.
My question is.. Can I get the db to tell me if there is a low score on a hole or do I have to do that with PHP after collecting the data?
[text]Here's an example of hole by hole data, the 3 on the first hole and 4 on the 6th hole are skins:
Holes----------------1 --2 --3 --4 --5 --6 --7 --8 --9
Player1--------------6 --5-- 5 --6 --7-- 7 --6 --5-- 5
Player2--------------3 --4 --5 --6-- 5 --5 --6 --7 --6
Player3--------------9--12 --5 --5 --6 --5 --5 --6 --6
Player4--------------5 --6 --7 --6 --7 --6 --5 --6 --7
Player5--------------4 --4 --5 --5 --5 --4 --5 --5 --5
Player6--------------6 --5 --5 --6 --6 --5 --5 --5 --5 [/text]
Thanks for your help