SQLite Forum

Can this simple query be optimized?
One problem with the query as 

SELECT x, min(y) FROM foo GROUP BY (x/10)
SELECT x, max(y) FROM foo GROUP BY (x/10);

is that you can't tell whether the x you get is related to the max or the min of the group (without doing another query on the results). You really need 5 values all together in one row of output. This CTE query gives you all the information in a simple and easy to understand query:

sqlite> WITH bar1(x_miny, miny, xd10) AS
   ...> ( SELECT x, min(y), (x/10) FROM foo GROUP BY (x/10) )
   ...> ,bar2(x_maxy, maxy, xd10) AS
   ...> ( SELECT x, max(y), (x/10) FROM foo GROUP BY (X/10) )
   ...> SELECT bar1.x_miny, bar1.miny, bar2.x_maxy, bar2.maxy, bar1.xd10
   ...>   FROM bar1 JOIN bar2 ON bar1.xd10=bar2.xd10;

I don't believe you can get all the required information without scanning twice because the max and mins will (almost always) be in different rows of foo. The use of a temporary table adds the equivalent of a scan to load the temp table, so there is no performance gain using it.

As an aside, if other SQL engines don't return correlated fields for aggregate queries (the x in the SELECT), how can you get an x?