MySQL MIN/MAX all row

问题:

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 ?

回答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

回答2:

Subquery is your bet,

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

回答3:

Select * from Races
where TotalCP = SELECT MIN( TotalCP ) FROM Races