Returning a Min() and another field?

1291 views mysql
5

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

answered question

2 Answers

1

I think you need to add l.hashtag in 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

NOTE

when you use aggregate function, you might add non-aggregate columns in group by

posted this
8

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

posted this

Have an answer?

JD

Please login first before posting an answer.