MySQL Greatest-N-Per-Group – Optimizing SQL to Retrieve Rows with Minimum Column Value

greatest-n-per-groupMySQL

I've worked out how to get some rows from a table where a column has the lowest possible value. Note this can return multiple rows, which is what I want. It works just fine.

SELECT col1, col2
FROM TableA
WHERE col4=0
    AND col5=1
    AND col3 IN
       (SELECT MIN(col3)
        FROM TableA
        WHERE col4=0 AND col5=1)

But is the nested select overkill, i.e. can this be optimised? Maybe there's a simple SQL command I'm missing…

I tried

SELECT col1, col2, MIN(col3)
FROM TableA
WHERE col4=0
    AND col5=1

but this will only ever return one row.

Best Answer

The documentation provides a couple ways to get what you want, which will perform well. 

SELECT TableA.col1, TableA.col2
FROM TableA
INNER JOIN (
    SELECT
    MIN(col3) AS col3
    FROM TableA
    WHERE col4 = 0
    AND col5 = 1
) lowest_col3_TableA
ON lowest_col3_TableA.col3 = TableA.col3
WHERE TableA.col4=0
AND TableA.col5=1

Or

SELECT TableA.col1, TableA.col2
FROM TableA
LEFT OUTER JOIN TableA lower_col3_TableA
ON lower_col3_TableA.col4 = 0
AND lower_col3_TableA.col5 = 1
AND lower_col3_TableA.col3 < TableA.col3
WHERE TableA.col4=0
AND TableA.col5=1
AND lower_col3_TableA.col3 IS NULL

For this second query, the last line of AND lower_col3_TableA.col3 IS NULL doesn't necessarily need to be col3, but DOES need to be a column that is defined with NOT NULL. Usually the leftmost part of the primary key is best.

Related Question