0

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.