Using GREATEST and GROUP BY together

franwpd

I have a table that lists the name, pins1, pins2, pins3 and gender.

I want to choose the top 20 unique women members with the highest scores. In other words, if the individuals have multiple high scores, only show their name with the highest score. Here is what I have tried.

SELECT name, GREATEST(pins1, pins2, pins3) AS high_score FROM bc_average WHERE gender = 'Women' ORDER BY high_score DESC LIMIT 20

Echos the top scores in decending order but contains duplicate names.

SELECT memberid, name, GREATEST(pins1, pins2, pins3) AS high_score FROM bc_average WHERE gender = 'Women' GROUP BY memberid ORDER BY high_score DESC LIMIT 20

Adding GROUP BY echos unique names but not the member's top scores.

SELECT DISTINCT name, GREATEST(pins1, pins2, pins3) AS high_score FROM bc_average WHERE gender = 'Women' ORDER BY high_score DESC LIMIT 20

DISTINCT name does not eliminate duplicates either.

Any Suggestions???

bradgrafelman

Why do you have the numbered columns "pins1", "pins2", and "pins3" ? Is there a reason you have exactly three? I ask because numbered columns like this often a very reliable sign that the database structure is not normalized.

The problem here:

SELECT memberid, name, GREATEST(pins1, pins2, pins3) AS high_score FROM bc_average WHERE gender = 'Women' GROUP BY memberid ORDER BY high_score DESC LIMIT 20

is that you didn't tell the SQL server you wanted the row with the largest "high_score", so it's probably just going to pick whichever one it comes across first (which may or may not be deterministic behavior). You'd probably want to surround that call to GREATEST() with an aggregate function like MAX() to get the largest value in the group of rows (e.g. the largest "high_score" for each memberid value).

However, the query would probably be a bit simpler if you had a normalized schema.

franwpd

The pins1, pins2, pins3 represent the scores of the three games played by a bowler for that night. I see what you mean about the server picking whichever one comes first. Because I am not an expert programmer, I struggle with the concepts here so please bear with me. This is how I interpreted your answer:
SELECT name, MAX(GREATEST(pins1, pins2, pins3)) AS high_score FROM bc_average WHERE gender = 'Women' ORDER BY high_score DESC LIMIT 20
This produced only one member.

bradgrafelman

What happened to the GROUP BY clause?

franwpd

oops :o

Still not quite right though. It echos unique names not in order of highest scores.

franwpd

oops :o

Still not quite right though. It echos unique names not in order of highest scores.

franwpd

Oh wait... Final answer:

SELECT name, MAX(GREATEST(pins1, pins2, pins3)) AS high_score FROM bc_average WHERE gender = 'Women' GROUP BY memberid ORDER BY high_score DESC LIMIT 20

Works! THANKS!!!!!!!!!