UNION Sorts on First Field
(1) By Cecil (CecilWesterhof) on 2021-04-12 09:28:40 [link] [source]
When I execute:
SELECT 'Minimum' UNION SELECT 'Maximum' UNION SELECT 'Today' ;
Maximum Minimum Today
While I would expect:
Minimum Maximum Today
Is this a bug or the way it should work?
Is it possible to circumvent the sort?
(2) By Gunter Hick (gunter_hick) on 2021-04-12 09:54:16 in reply to 1 [link] [source]
UNION requires that duplicate records be omitted, which implies a sort. UNION ALL does not require a sort and will return records in visitation order. It is wrong to assume a specific order of records returned by a SELECT. If you require a specific order, you must provide an ORDER BY clause.
(3) By Cecil (CecilWesterhof) on 2021-04-12 10:11:34 in reply to 2 [link] [source]
With UNION ALL it goes correct. But it can go haywire any-time?
I do not know of an ORDER BY clause that will give the correct sequence. ;-)
But at the moment it works.
When it does not any-more I could prepend '1 ', '2 ' and '3 ' to them.
(4.1) By Gunter Hick (gunter_hick) on 2021-04-12 10:33:35 edited from 4.0 in reply to 3 [source]
Not "any time", only when the visitation order changes. UNION ALL evaluates/groups from left to right. You could use a "sort order" table CREATE TABLE sort_order (Id INTEGER, name TEXT UNIQUE); INSERT INTO sort_order VALUES (1, 'Minimum'),(2, 'Maximum'), (3, 'Today'); and then SELECT name FROM sort_order ORDER BY Id;
(5) By Richard Hipp (drh) on 2021-04-12 11:26:09 in reply to 1 [link] [source]
If there is no ORDER BY clause, the SQL engine (any SQL engine, not just SQLite) is free to return the rows in any order it wants. That order might change from one update of the database engine to the next, or from one database to the next, or even on two consecutive runs of the same query.
If you need a particular output order, then you must use an ORDER BY clause.