Thread: order by and union

In postgres 7.0.2 i have an "order by" with column names like this :

select colonne1,colonne2 from A
union
select colonne1,colonne2 from B

    order by colonne1,colonne2

In postgres 7.1 this simple example works. But with i have a more
difficult select that doesn't work, and i had to use the number column
to get a good result

This is my real example :

select nmn_can,pre_can,dat_nai,dci_sai,cnd.cod_spe,cnd.cod_pa1,decision
, cnd.cod_pa2
from  cnd,cnd_grp, decision where cnd.cod_eta='0731043M'
and cnd.num_can=cnd_grp.num_can and cnd_grp.typ_grp='D'
and cnd_grp.dci_sai=decision.cod_dec

union

select nmn_can,pre_can,dat_nai,dci_sai,cnd.cod_spe,cnd.cod_pa1,decision
, cnd.cod_pa2
from cnd,cnd_grp, decision where cnd.num_can=cnd_grp.num_can and
cnd_grp.typ_grp='D'
and cnd_grp.dci_sai=decision.cod_dec and cnd.cod_cat='520'
and cnd.cod_eta in ( select cod_aff from affichage where
cod_eta='0731043M' and cnd.cod_spe=affichage.cod_spe)

order by cnd.cod_spe, cnd.cod_pa1,decision,nmn_can

so i replace the orber by :

order by 5,6,7,1


Benoit Lion - Inspection Académique de Chambéry - tel :04 79 62 53 19
France

Benoit Lion writes:

> select nmn_can,pre_can,dat_nai,dci_sai,cnd.cod_spe,cnd.cod_pa1,decision
> , cnd.cod_pa2
> >from  cnd,cnd_grp, decision where cnd.cod_eta='0731043M'
> and cnd.num_can=cnd_grp.num_can and cnd_grp.typ_grp='D'
> and cnd_grp.dci_sai=decision.cod_dec
>
> union
>
> select nmn_can,pre_can,dat_nai,dci_sai,cnd.cod_spe,cnd.cod_pa1,decision
> , cnd.cod_pa2
> >from cnd,cnd_grp, decision where cnd.num_can=cnd_grp.num_can and
> cnd_grp.typ_grp='D'
> and cnd_grp.dci_sai=decision.cod_dec and cnd.cod_cat='520'
> and cnd.cod_eta in ( select cod_aff from affichage where
> cod_eta='0731043M' and cnd.cod_spe=affichage.cod_spe)
>
> order by cnd.cod_spe, cnd.cod_pa1,decision,nmn_can
>
> so i replace the orber by :
>
> order by 5,6,7,1

This is correct.  The table names from the union branches are not in
scope for the order by.  Consider this case

select cnd.cod_spe from cnd
union
select cnd.cod_spe from (select relname from pg_class) as cnd (cod_spe)
order by cnd.cod_spe;

Which "cnd" is meant here?  However, you should be able to write

order by cod_spe;

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter