Select lowest number in a column

[MrTee]

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:

$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';";

This query returns all the info needed except for one thing...

I need to know if there is a single 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 the query a score for each hole.

 

In order to determine if there is a single low score on a 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?

If it can be done in the query, please point me in the right direction.

 

 

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 --7 --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

 

Thanks for your help 

[ale8oneboy]

Take a look at the MySQL's min() function. It will return what ever is the lowest value in the field you tell it to use.

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_min

[MrTee]

Thank you ale8oneboy,

 

The min() function returns the min value of the column but not the unique min value.

My interpretation of unique here is: "there is only one of that value in the result set and it is the lowest value". Back to the golf game... this would be a skin. 

The min() query is returning the lowest value whether it is, or is not, unique.

 

The other problem I have is that when I add the min syntax, the query only returns one row with the first player info and all the minimum hole values. I'm sure the query can be changed to include all rows with player info but if it doesn't return the unique min value, it doesn't matter.

 

$sql = "
SELECT stats_h_h.*, MIN(DISTINCT stats_h_h.hh1) as hole1skin, 
MIN(DISTINCT stats_h_h.hh2) as hole2skin, 
MIN(DISTINCT stats_h_h.hh3) as hole3skin, 
MIN(DISTINCT stats_h_h.hh4) as hole4skin, 
MIN(DISTINCT stats_h_h.hh5) as hole5skin, 
MIN(DISTINCT stats_h_h.hh6) as hole6skin, 
MIN(DISTINCT stats_h_h.hh7) as hole7skin,
MIN(DISTINCT stats_h_h.hh8) as hole8skin, 
MIN(DISTINCT stats_h_h.hh9) as hole9skin,
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 = 'Putt Boys') 
AND stats.playDate = '2011-02-08' 
AND stats_h_h.courseNum = '26';";

 

Unless someone has a better idea, I'm just going to use PHP after the query to get the results I need.

 

Thanks again for your input.

[PFMaBiSmAd]

If you get and GROUP the rows using the MIN() value, the groups HAVING a COUNT() of 1 would match what you are looking for.

[fenway]

Yes -- you'll have to remove the duplicates first.