Returning a Min() and another field? [duplicate]

This question already has an answer here:

Retrieving the last record in each group - MySQL 24 answers

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

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