8/6/2023 0 Comments Sqlite order by length unionThis is mostly because the syntactical order of operations doesn't match the logical order of operations. Which is what most people really want, when they ORDER BY something they cannot really order by. In a way, this is syntax sugar for this: SELECT length What we're essentially doing is, we take all distinct lengths, and for each group of identical lengths, we're taking the top title as a criteria to order by. Well, this is: SELECT DISTINCT ON (title) length FROM film ORDER BY title Remember how this wasn't possible? SELECT DISTINCT length FROM film ORDER BY title PostgreSQL has a nice feature for when you want to order by something from within a group of non-distinct values. Or start using aliases: SELECT DISTINCT a - b AS x FROM t ORDER BY abs(x + x) PostgreSQL DISTINCT ON For example, we could even do this in Oracle: SELECT DISTINCT a - b FROM t ORDER BY abs((a - b) + (a - b)) The expression in the projection is A - B, and that's the only expression we can re-use in the ORDER BY. We could derive a complicated transformation of expressions that can be reasonably transformed into each other, such as A - B = -(B - A), but this simply isn't practical. Now, how are we supposed to order these by A, B or B - A? It looks as though we should be able to sort by B - A in this case. Clearly, the data set we want is: A - B A B B - A It is easy to build an intuition for why these don't work. SELECT DISTINCT a - b FROM t ORDER BY b - a But these don't work: SELECT DISTINCT a - b FROM t ORDER BY a The select list contains a column A - B, so we can derive any ORDER BY expression from it. This also works in Oracle: SELECT DISTINCT a - b FROM t ORDER BY abs(a - b) We are allowed to write: SELECT DISTINCT a, b FROM t ORDER BY a - b Īgain, the order by expressions can be derived completely from the select list. Totally fine, because we can derive all of these order by expressions from expressions in the select list
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |