Why query the last ten when you can query every row

April 02, 2025

Here’s another SQL query I wrote recently. Lets say you want to query the minimum stock price of a given symbol out of the last ten values. Here was my first take, can you spot the bug?

SELECT MIN(price) FROM data WHERE symbol = 'APPL' ORDER BY id DESC LIMIT 10;

Breaking down my thinking:

I want the minimum price: MIN(price)
For a given symbol: WHERE symbol = 'APPL'
The latest values: ORDER BY id DESC
The last ten values: LIMIT 10

I eventually realised that this query was incorrect. It was returning the minimum price across all rows, not just the last ten.

The answer lies in how the SQL query is parsed. If I had to visualise it, I would put it this way. The query was being executed like so:

[[SELECT MIN(price) FROM data WHERE symbol = 'APPL' ORDER BY id DESC]] LIMIT 10;

In other words, it was grabbing the minimum value over all rows (which returns… a row) and then limiting that result to ten rows, which produces a single row anyway. The correct way would be to query the last ten values, THEN calculate the minimum value:

SELECT MIN(price)
FROM (
    SELECT price
    FROM data
    WHERE symbol = 'APPL'
    ORDER BY id DESC
    LIMIT 10
) AS last_k_values;

Tests would also have covered this issue :)