Re: Clarify the ordering guarantees in combining queries (or lack thereof)
| От | Shay Rojansky |
|---|---|
| Тема | Re: Clarify the ordering guarantees in combining queries (or lack thereof) |
| Дата | |
| Msg-id | CADT4RqCGMsRpW-76yhnzxJokjgOuo-faNu68Vdq4Lf3AKWPybg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Clarify the ordering guarantees in combining queries (or lack thereof) ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: Clarify the ordering guarantees in combining queries (or lack thereof)
Re: Clarify the ordering guarantees in combining queries (or lack thereof) |
| Список | pgsql-docs |
>> I was trying to understand what - if any - are the guarantees with regards to ordering for combining queries (UNION/UNION ALL/...). From this message[1], it seems that UNION ALL does preserve the ordering of the operand queries, whereas UNION does not (presumably neither do INTERSECT, INTERSECT ALL, EXCEPT and EXCEPT ALL).
>>
>> The documentation[2] makes no mention of this, I'd suggest adding a note clarifying this.
>
> If you want ordered output use ORDER BY.
I don't see how that could be done. Consider the following:
(SELECT id FROM data ORDER BY id)
UNION ALL
(SELECT id FROM data ORDER BY id DESC);
UNION ALL
(SELECT id FROM data ORDER BY id DESC);
If there's a guarantee that UNION ALL preserves ordering - as Tom seems to indicate in the thread quoted above - then the above works. If there's no such guarantee, then AFAIK the above can't be rewritten; putting the ORDER BY outside - on the results of the UNION ALL - would order all results rather than preserving each resultset's ordering.
В списке pgsql-docs по дате отправления: