SQLite Query – Selecting Correct Non-Aggregate Columns with MAX Aggregate

sqlite

I am confused about the max() aggregator, I think this query makes sense…

select title, vote_avg, floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...> from films
   ...> where (vote_avg, decade) in (
   ...>   select max(vote_avg), floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...>   from films
   ...>   where vote_cnt > 100
   ...>   group by decade
   ...> )
   ...> and vote_cnt > 100
   ...> group by decade
   ...> order by decade desc;
title                            vote_avg  decade
-------------------------------  --------  ------
Homecoming: A Film by Beyoncé    8.6       2010  
Spirited Away                    8.5       2000  
Dilwale Dulhania Le Jayenge      8.9       1990  
The Empire Strikes Back          8.4       1980  
We All Loved Each Other So Much  8.6       1970  
Psycho                           8.4       1960  
12 Angry Men                     8.4       1950  
The Great Dictator               8.4       1940  
City Lights                      8.4       1930  
Sherlock Jr.                     8.2       1920  
The Immigrant                    7.5       1910  
The Great Train Robbery          7.2       1900

but I am not sure why this one works?

select title, max(vote_avg), floor(cast(substr(date, 1, 4) as integer)/10)*10 as decade
   ...> from films
   ...> where vote_cnt > 100
   ...> group by decade
   ...> order by decade desc;
title                            max(vote_avg)  decade
-------------------------------  -------------  ------
Homecoming: A Film by Beyoncé    8.6            2010  
Spirited Away                    8.5            2000  
Dilwale Dulhania Le Jayenge      8.9            1990  
The Empire Strikes Back          8.4            1980  
We All Loved Each Other So Much  8.6            1970  
Psycho                           8.4            1960  
12 Angry Men                     8.4            1950  
The Great Dictator               8.4            1940  
City Lights                      8.4            1930  
Sherlock Jr.                     8.2            1920  
The Immigrant                    7.5            1910  
The Great Train Robbery          7.2            1900

I'm using sqlite3, which of these queries is better practice? (Or are they both incorrect?)

Best Answer

Such a bare column in an aggregate query is not allowed by the SQL standard. But SQLite allows it, and returns the intuitively correct result:

When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum.

If you want to write portable SQL, don't use this feature.

Related Question