SQLite Forum

Can this simple query be optimized?
Login
Very correct Keith.

I was able to achieve a "single scan" of `foo` with a similar approach to Ryan using window functions and a temp table like this:

```
SELECT first_value(x) OVER win x_min_y,
       last_value(x) OVER win x_max_y,
       first_value(y) OVER win min_y,
       last_value(y) OVER win max_y
  FROM foo
WINDOW win AS (PARTITION BY x/10 ORDER BY y ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
```

However the overhead of such a query is worse than the OPs original query, expecially if an appropriate index exists i.e.

```sql
CREATE INDEX idx ON foo(x/10, y);

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