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
