SQLite Forum

Can this simple query be optimized?
True, but I doubt that's what the OP meant to get.

If it is, his original query relies on a special quirk of SQLite that is not guaranteed to work the same in future, and will not work in any other SQL engine.

If it *IS* the case, he is better off with:

WITH M(x, y, miny, maxy) AS (
    SELECT x, y, 
           min(y) OVER (PARTITION BY (X/10)), 
           max(y) OVER (PARTITION BY (X/10))
      FROM foo
SELECT x, y FROM M WHERE y = miny
SELECT x, y FROM M WHERE y = maxy

And there are no shortcuts to make that happen in a single loop I think. Perhaps persisting the subquery/CTE would be slightly faster?