Why does the phrase order matter with 'union' ?

[alan232]

Hi All ;

The first select...union...select produces the correct result of about 15 records. The second select...union...select produces only one record (the most recent) when it should produces all the records as the first one does.

This has me stumped:

* This WORKS
select zICD9.cCode, zICD9.cDesc, zICD9.cICD9, max(zPMH.tService), m.lcPatient ;
from zPMH, zICD9 ;
where zPMH.cICD9 = zICD9.cICD9 ;
and zPMH.cAccount = m.lcPatient ;
union ;
select ;
zICD9.cCode, zICD9.cDesc, zICD9.cICD9, max(zItems.tfrom), m.lcPatient ;
from zItems, zDx, zICD9 ;
where zItems.cItem = zDx.cItem ;
and zDx.cICD9 = zICD9.cICD9 ;
and zItems.cPatient = m.lcPatient ;
group by 1 ;
order by 4 desc

* THIS FAILS!
* most recent occurance without duplication, in descending time order.
select ;
zICD9.cCode, zICD9.cDesc, zICD9.cICD9, max(zItems.tfrom), m.lcPatient ;
from zItems, zDx, zICD9 ;
where zItems.cItem = zDx.cItem ;
and zDx.cICD9 = zICD9.cICD9 ;
and zItems.cPatient = m.lcPatient ;
union ;
select zICD9.cCode, zICD9.cDesc, zICD9.cICD9, max(zPMH.tService), m.lcPatient ;
from zPMH, zICD9 ;
where zPMH.cICD9 = zICD9.cICD9 ;
and zPMH.cAccount = m.lcPatient ;
group by 1 ;
order by 4 desc

Any help is appreciated!
Thanks,
ajw

 

[alan232]

Hi All;

Got it!
The 'group by' clause only affects the most immediate preceeding 'select', not the overall union. Hence, only one record from the 'select' prior to the union was being included in the output. ('order by' does apply to the overall union.)

bye for now,
ajw

 

[alvechurchdata]

You might do better to solve the problem in two stages. Do the selection and union in one query and then do the ordering and grouping in a second query.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[alan232]

Hi alvechurchdata;

Good idea...Thanks!
ajw