> Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression.
Does `max` somehow only affect the selected rows? Or is this relying on a side affect of the query planner sorting the table to optimize max?
> If there is exactly one min() or max() aggregate in the query, then all bare columns in the result set take values from an input row which also contains the minimum or maximum.
In all of the nearby examples contain an explicit "GROUP BY" clause but I don't think that this section says that one is required for this behaviour. So I guess this is the behaviour that is being described.
However I found this rule as well:
> If the same minimum or maximum value occurs on two or more rows, then bare values might be selected from any of those rows. [...] The choice might be different for different bare columns within the same query.
Which is in conflict with the earlier rule which says that the row is consistent. Or is this consistent row rule only provided for the implicit grouping. AKA is a and b guaranteed to be from the same row for the first query but not the second? That would be very surprising, maybe the docs just promise too little?
SELECT a, b, MAX(c)
FROM t
SELECT 1 as group, a, b, MAX(c)
FROM t
GROUP BY 1
There are also more not-well sepcified results if multiple of MIN or MAX are used or if these functions or customized. So overall it is probably best to avoid this in "production" use. But can be convenient for some quick exportation if you are careful.
From https://www.sqlite.org/lang_select.html#generation_of_the_se...
> Each non-aggregate expression in the result-set is evaluated once for an arbitrarily selected row of the dataset. The same arbitrarily selected row is used for each non-aggregate expression.
Does `max` somehow only affect the selected rows? Or is this relying on a side affect of the query planner sorting the table to optimize max?
However then I found https://www.sqlite.org/lang_select.html#bare_columns_in_an_a...
> If there is exactly one min() or max() aggregate in the query, then all bare columns in the result set take values from an input row which also contains the minimum or maximum.
In all of the nearby examples contain an explicit "GROUP BY" clause but I don't think that this section says that one is required for this behaviour. So I guess this is the behaviour that is being described.
However I found this rule as well:
> If the same minimum or maximum value occurs on two or more rows, then bare values might be selected from any of those rows. [...] The choice might be different for different bare columns within the same query.
Which is in conflict with the earlier rule which says that the row is consistent. Or is this consistent row rule only provided for the implicit grouping. AKA is a and b guaranteed to be from the same row for the first query but not the second? That would be very surprising, maybe the docs just promise too little?
There are also more not-well sepcified results if multiple of MIN or MAX are used or if these functions or customized. So overall it is probably best to avoid this in "production" use. But can be convenient for some quick exportation if you are careful.