Thanks Tom it worked. rgds Antony Paul On Mon, 10 Jan 2005 08:39:50 -0500, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Richard Huxton <dev@xxxxxxxxxxxx> writes: > > Antony Paul wrote: > >> I need to use ORDER BY clause in a UNION query and the Order BY > >> columns are not included in the SELECT statement. I tried like this > >> > >> (select .... from a) UNION (select ..... from b) order by a.ename; > >> > >> It says that > >> ERROR: Attribute "ename" not found > > > The "order by" is applying to the results of the union, not one of the > > sub-selects. If you want to sort by a value, you'll need to include it > > in the results list. > > You could suppress the order-by fields after the fact: > > SELECT x,y,z FROM > ( (SELECT x,y,z,q FROM a) > UNION > (SELECT x,y,z,q FROM b) > ORDER BY q > ) ss; > > Also, always ask yourself if you really need UNION or if UNION ALL > is sufficient. Removing duplicates from a large UNION is *expensive*, > and all too often a waste of time. > > regards, tom lane > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
