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 ? > 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). 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; Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
