![]() Thanks for such a detailed explanation especially the detailed step by step explanation. SELECT length FROM film GROUP BY length ORDER BY MAX(title) …or even exclude MAX(title) from the result set SELECT length, MAX(title) FROM film GROUP BY length ORDER BY 2 SELECT length, MAX(title) FROM film GROUP BY length ORDER BY MAX(title) SELECT length, MAX(title) FROM film GROUP BY length However, if we apply aggregation to”title”(if it ever makes sense) we will get the following perfectly valid query: it's ambiguous what row will yields "title", it's not a part of grouping. SELECT length, title FROM film GROUP BY length …because you can’t write even such “unordred” query ![]() SELECT length FROM film GROUP BY length ORDER BY title However, the below query is invalid because “title” is neither a part of grouping nor a result of aggregate function (like MAX or MIN) : SELECT length FROM film GROUP BY length ORDER BY 1 …or just by referring field in SELECT clause by number: SELECT length FROM film GROUP BY length ORDER BY length Totally fine, because we can derive all of these order by expressions from expressions in the select listĪctually, I would use the following explanation. ORDER BY clause, ordering by the mod(LENGTH, 10), LENGTH columns.DISTINCT clause, removing duplicate LENGTH values … all fine, because we don’t have the verboten extended sort key columns.MOD(LENGTH, 10) does not have to be put in the extended sort key columns, because it can be fully derived from the select list. SELECT clause, projecting the LENGTH column from the select list.How to interpret this? We’re looking again at the order of SQL operations: PostgreSQL doesn’t allow this because the expression MOD(LENGTH, 10) is not in the select list. … and finally discard it, because we never wanted it length | … we can now order by that column length |title | We get that synthetic extended sort key column TITLE along with the LENGTH column that we requested length |title | Database optimisers may choose other ways to implement this. SELECT clause (implicit), projecting only the LENGTH column, discarding the TITLE columnĪgain, this is what happens logically.ORDER BY clause, ordering by the TITLE column.SELECT clause, projecting the LENGTH column from the select list and the TITLE from the extended sort key columns.There is a concept called extended sort key columns in the SQL standard, which means the above query has a slightly different order of operations (apart from the fact that there is no DISTINCT operation): Now we’re getting a new random order (due to hashing) and no duplicates anymore: length |Įven if after projecting the LENGTH column, it seems as though it is no longer available for sorting, it really is, according to the SQL standard and to common sense. ![]() The intermediary data set is something like: length | The intermediary data set is something like: film_id |title |length |. If we look at this step by step, we have:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |