SQL查詢最大聚合選擇正確的非聚合列？

我對 max() 聚合器感到困惑，我認為這個查詢很有意義……

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  

但我不確定為什麼這個有效？

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  

我正在使用 sqlite3，這些查詢中哪個更好？（或者它們都是不正確的？）

SQL 標準不允許在聚合查詢中使用這種裸列。但 SQLite允許它，並返回直覺正確的結果：

當在聚合查詢中使用 min() 或 max() 聚合函式時，結果集中的所有裸列都從包含最小值或最大值的輸入行中獲取值。

如果您想編寫可移植的 SQL，請不要使用此功能。

引用自：https://dba.stackexchange.com/questions/289206