Returning a Min() and another field?

Question

I'm building a leaderboard and want to return a MIN() value - however, I also want another field which is on the same row as the min value.

I have this so far:

SELECT u.username, MIN(timer) AS intScore, l.hashtag 
        FROM users u 
        JOIN leaderboard l ON l.users_id = u.users_id 
        GROUP BY u.username
        ORDER BY 
        intScore ASC

...this returns the correct MIN intScore, but not the correct hashtag.

The db looks liks this:

Leaderboard DB:

users_id, timer, hashtag
1, 10, TEST
2, 12, TEST
1, 20, TEST
3, 15, TEST
1, 5, LIVE
4, 20, LIVE

and I want the result to be:

users_id, timer, hashtag
1, 5, LIVE
2, 12, TEST
3, 15, TEST
4, 20, LIVE

Any help would be appreciated

Guy Louzon · Accepted Answer · 2018-09-26T10:07:37.367

1

The thing is this: any aggregate function "doesn't care" about the rest of its line If it wasn't MIN but SUM, its easier to see...

The solution is a bit tricky, you should do something that involves LEFT JOIN with an inequality :

SELECT u.username, l.timer AS intScore, l.hashtag

FROM
 leaderboard l
 INNER JOIN users u ON u.users_id = l.users_id
 LEFT JOIN leaderboard l2 ON l.users_id = l2.users_id AND l2.timer < l.timer
WHERE
 l2. users_id IS NULL
ORDER BY intScore ASC

The idea is getting lowest value by skipping the MIN function and getting the entire row

Filtering by date (or any other column / criteria for that matter) from the leaderboard table, will require us to filter each table we use It's important to filter the LEFT JOINed table at the ON conditition, otherwise we eliminate the filtering NULLs effect:

SET @date0 = '2018-01-01';
SELECT u.username, l.timer AS intScore, l.hashtag

FROM
 leaderboard l
 INNER JOIN users u ON u.users_id = l.users_id
 LEFT JOIN leaderboard l2 ON l.users_id = l2.users_id AND l2.timer < l.timer AND l2.game_date >= @date0
WHERE
 l.game_date >= @date0
 l2.users_id IS NULL
ORDER BY intScore ASC

hope it helps

edited Sep 26 '18 at 10:07

answered Sep 15 '18 at 19:55

Guy Louzon

1,175
9
19

Nice one! But I don't think this would return a result if there were only one database entry? – ojsglobal Sep 16 '18 at 07:53
Why not ? if there is only one entry, then JOINed table will be null anyway, and you'll get the only record – Guy Louzon Sep 16 '18 at 07:54
I don't know, it just doesn't return a result if there is only one entry! – ojsglobal Sep 17 '18 at 18:17
weird, since its left join with an inequality, in the case of 1 row it will reurn the 1 row... I need to see the tablea to check it – Guy Louzon Sep 17 '18 at 18:34
How would I add a date field to this? So I could look for the best scores after a certain date? – ojsglobal Sep 26 '18 at 08:55
Where's the date in the table ? and it's possible, but a little trickier... – Guy Louzon Sep 26 '18 at 08:57
I know, I've been ripping my hair out since you gave me your solution! The date is in the leaderboard table, called game_date (y-m-d), each entry has the game date it was played. – ojsglobal Sep 26 '18 at 09:13
I'll take that as a compliment...:) (how about an upvote ? ;)) I'll try to play with my query for a solution – Guy Louzon Sep 26 '18 at 09:15
Your question was regarding minimal value, isn't the best score the maximal value ? – Guy Louzon Sep 26 '18 at 10:01
I updated the answer – Guy Louzon Sep 26 '18 at 10:07
1

The best score is actually the lowest score, it's a timer - so the quickest time. :) – ojsglobal Sep 26 '18 at 10:16

Returning a Min() and another field?

1 Answers1

Linked