Are there in guarantees about the non-aggregated columns in a GROUP BY query?

Viewed 155

Let's assume we have the following table and query in SQLite:

id val parent letter
1 3 10 a
2 3 10 b
3 0 10 c
4 5 20 d
SELECT id, MAX(val), parent, letter FROM table GROUP BY parent

Are there any guarantees about the value of id? In MySQL there is even a mode which forbids selecting non-aggregated values. If there is no such guarantee, is it possible to somehow get a single row per parent?

id MAX(val) parent letter
1† 3 10 a
4 5 20 d

† or 2 (does not matter as long as letter is from the same row)

2 Answers

This behavior is covered in SELECT/Simple Select Processing/Side note: Bare columns in an aggregate queries.

In your query the columns id and letter, which are not aggregated and are not included in the GROUP BY clause, are called bare columns.
Because you use the MAX() aggregate function, the values of these 2 columns:

... take values from the input row which also contains the minimum or maximum

But, since there may exist more than 1 rows with the maximum val for the same parent:

There is still an ambiguity if two or more of the input rows have the same minimum or maximum value

This means that for your sample data there is no guarantee that for parent = 10 you will get the row with id = 1 in the results.
You may get the row with id = 2 which also contains the maximum val.

Assuming that in such a case, where for the same parent there may exist more than 1 rows with the maximum val, you want the row with the minimum id, you can do it with window functions:

SELECT id, val, parent, letter
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parent ORDER BY val DESC, id) rn
  FROM tablename
)
WHERE rn = 1

or:

SELECT DISTINCT
       FIRST_VALUE(id) OVER (PARTITION BY parent ORDER BY val DESC, id) id, 
       MAX(val) OVER (PARTITION BY parent) val, 
       parent, 
       FIRST_VALUE(letter) OVER (PARTITION BY parent ORDER BY val DESC, id) letter
FROM tablename 

See the demo.

From the Official doc

Worth a careful read. The bold is what you are looking for.

Aggregate Queries Can Contain Non-Aggregate Result Columns That Are Not In The GROUP BY Clause

In most SQL implementations, output columns of an aggregate query may only reference aggregate functions or columns named in the GROUP BY clause. It does not make good sense to reference an ordinary column in an aggregate query because each output row might be composed from two or more rows in the input table(s).

SQLite does not enforce this restriction. The output columns from an aggregate query can be arbitrary expressions that include columns not found in GROUP BY clause. This feature has two uses:

  1. With SQLite (but not any other SQL implementation that we know of) if an aggregate query contains a single min() or max() function, then the values of columns used in the output are taken from the row where the min() or max() value was achieved. If two or more rows have the same min() or max() value, then the columns values will be chosen arbitrarily from one of those rows.

...

Now, with your query

SELECT id, MAX(val), parent, letter FROM table GROUP BY parent

You basically have id and letter that are not aggregated and not in the GROUP BY.

Which means that the values returned for them are those from the row(s) matching the MAX(val).

And in your case, there are 2 of them

id  val parent  letter
1   3   10      a
2   3   10      b

Which means that sqlite will arbitrarily (randomly) return the values of either row 1 or 2. So no, you have no guarantees.

Related