See the [db-fiddle][1].
On the following table
```
CREATE TABLE foo (x INTEGER PRIMARY KEY, y INTEGER);
INSERT INTO foo VALUES (0,41), (1, 23), (2,45), (3,32), ...
```
I need the `x` and `y` which has `min(y)` over groups of 10 `x`, and the same for `max(y)`:
```
SELECT x, min(y) FROM foo GROUP BY (x/10)
UNION
SELECT x, max(y) FROM foo GROUP BY (x/10);
```
The `EXPLAIN QUERY PLAN` output shows that two scans of the table are performed
```
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| |--SCAN TABLE foo
| `--USE TEMP B-TREE FOR GROUP BY
`--UNION ALL
|--SCAN TABLE foo
`--USE TEMP B-TREE FOR GROUP BY
```
**Is there any way to reword the query so that only one scan is performed?**
What I've done in the mean time is to select all rows (`SELECT x, y FROM foo;`) and manually aggregate min/max as rows are returned to the host language (C++):
```
int lastGroup = 0;
while (sqlite3_step(query) == SQLITE_ROW) {
int x = sqlite3_column_int(query, 0);
int y = sqlite3_column_int(query, 1);
int group = x / 10;
if (group != lastGroup) {
// save minX, minY, maxX, maxY in a list somewhere
// reset minX, minY, maxX, maxY
// ...
lastGroup = group;
}
else {
if (y < minY) {
minX = x;
minY = y;
}
else if (y > maxY) {
maxX = x;
maxY = y;
}
}
}
```
This achieves a single scan and the whole process is more than twice as fast... but I'd rather express this logic *declaritively* in SQL if possible. I'm kind of surprised that sqlite doesn't recognize the opportunity to do this in one scan.
[1]: https://www.db-fiddle.com/f/xjHdxku1CMHm6ZY6LmwhB1/2