SQLite Forum

UNION Sorts on First Field
Login

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'
    ;

I get:

    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.