MySQL MIN/MAX all row

Question

I have table Races with the rows ID, Name and TotalCP . I SELECT MIN( TotalCP ) FROM Races, but then I want to select the entire row which have the minimum value. How I can make that, in a single query ?

Answer 1

The general form for getting a whole row from an aggregated value is:

SELECT *
FROM Races
WHERE TotalCP = (SELECT MIN(TotalCP) FROM Races)

or

SELECT r.*
FROM
(
    SELECT MIN(TotalCP) t
    FROM Races
) m
INNER JOIN Races r ON m.t = r.TotalCP

However, in this case, since you're using MIN, you can just sort and take the first row:

SELECT *
FROM Races
ORDER BY TotalCP
LIMIT 1