On Mon May 15, 2017 at 09:58:31PM +0100, Simon Slavin wrote: > > On 15 May 2017, at 9:30pm, no...@null.net wrote: > > > SELECT > > 1 AS col > > UNION ALL > > SELECT > > 0 AS col > > ORDER BY > > col > 0 DESC; > > Out of interest, intuitively rather than reading documentation, which > do you think should be done first ? Should SQL do both SELECTs and > the UNION ALL, then ORDER the result ? Or should SQL apply the ORDER > BY to the second SELECT only ?
Intuitively (or according the union knowledge I can usually hold in my brain) I think of the above as follows, purely because I know there can only be one ORDER by statement: ( SELECT 1 AS col UNION ALL SELECT 0 AS col ) ORDER BY col > 0 DESC; > > I've read "The ORDER BY clause" of [1] and I *think* that the following > > is what I am running into: > > > > "However, if the SELECT is a compound SELECT, then ORDER BY > > expressions that are not aliases to output columns must be exactly > > the same as an expression used as an output column. " > > Right. So the problem is that the "AS col" clauses apply to the > individual SELECT queries, not to the results of the UNION. The > UNION command unites the two individual queries but SQL doesn’t > provide any way to name the resulting column(s). Except that you *can* use the first SELECT aliases on their own... which shouldn't be possible at all if SQL doesn't provide a way to name the resulting columns. > Here’s another question about intuition, rather than reading > documentation. How many columns should this query return ? Or > should it result in an error ? > > > SELECT > > 1 AS betty > > UNION ALL > > SELECT > > 0 AS carlos; Ideally this would be an error, but I already know that it isn't so I can't really say what my intuition thinks :-) -- Mark Lawrence _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users