Hello,
a) it is right, to place an ORDER BY at the end of the statement. That is the
correct place to do this.
but
b) UNION doesn't mean anyway an sort. UNION mean "eliminate dups". But there are
several techniques to do that. One of them is "sort". So, if the development
crew chooses to change the elimination algorithme, that would be, in sight of an
UNION, ok .
Best wishes,
Regards,
Peter
---------------------------------------------------------------------------
Peter Paefgen
Landesamt für Datenverarbeitung und Statistik NRW.
Telefon: 0211 9449 2390
Fax: 0211 9449 8390
Mail: peter.paefgen@lds.nrw.de
-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Chris Mulcahy
Gesendet: Donnerstag, 16. November 2006 00:53
An: George Pavlov
Cc: Alexander Staubo; MicroUser; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] ORDER BY
On Wed, 2006-11-15 at 14:59 -0800, George Pavlov wrote:
Alas, this suggestion is wrong on two counts: (a) UNION expects a single
> ORDER BY that applies to the whole recordset and which has to come at
> the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes)
--
> maybe you are thinking UNION ALL? So, to follow your advice he may
want
> a query like this, although it seems quite silly and there still isn't
> an ironclad guarantee re. the final result sorting:
>
> select * from
> (select * from foo where name != 'Other' order by name) x union all
> select * from foo where name = 'Other'
>
>
Here ya go.
select 1 SortCol, * from foo where name != 'Other'
UNION ALL
select 2 SortCol, * from foo where name = 'Other'
order by SortCol;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
