Getting Distinct from union

franwpd

I am trying to get the four top scores for a bowling team. I have the names of all the members and three game scores (pins*)

SELECT name, pins1 FROM bc_averages WHERE league = '$list[league]' UNION ALL SELECT name, pins2 FROM bc_averages WHERE league = '$list[league]' UNION ALL SELECT name, pins3 FROM bc_averages WHERE league = '$list[league]' ORDER BY pins1 DESC LIMIT 4";

$sql_result = mysql_query($sql,$link) or die(mysql_error());

Right now I get 194 henry 192 henry 191 henry 190 john

I get all the scores in order (I think) but if one bowler gets three highest scores, he/she is shown three times. I need to show four individuals that have top scores.

I need 194 john 190 john 184 suzi 182 gloria

New but learning more each day!

laserlight

Why do you have columns named pins1, pins2 and pins3 instead of a table for pins scores?

franwpd

Because I am a newbe? I am not sure how that will make a difference. Can you help me understand? The pin1, pin2, pin3 are from the three games they bowled each nite.

laserlight

franwpd wrote:
I am not sure how that will make a difference. Can you help me understand? The pin1, pin2, pin3 are from the three games they bowled each nite.

If you do it that way, then you don't need the unions. You can select the names and the highest scores for each, then order by score and hence just get the top 4.

Generally, numbered columns are a sign that your database is not properly normalised.

NogDog

Maybe instead of the union:

SELECT name, GREATEST(pins1, pins2, pins3) AS high_score
FROM . . .
ORDER BY high_score DESC LIMIT 4

franwpd

Thank you laserlight for the explanation. It Helps me to understand.

NogDog...you are the bomb diggity! It worked!

Thank you all for your assistance!

The more I do, the more I learn.