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:
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
I think you need to add
group by, otherwise that only
group by u.username the result will not be your expectations.
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, l.hashtag ORDER BY intScore ASC
when you use aggregate function, you might add non-aggregate columns in
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
hope it helps