Re: Issue with adding ORDER BY to EXCEPT.

On Tue, 2005-06-07 at 12:16, Jaime Casanova wrote:
> > SELECT encounter.encounter_id, encounter_d.encounter_d_id
> > FROM encounter
> > JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id
> > EXCEPT
> > SELECT encounter.encounter_id, encounter_d.encounter_d_id
> > FROM encounter
> > JOIN encounter_d on encounter_d.encounter_id = encounter.encounter_id
> > JOIN p_l_d ON p_l_d.patient_id = encounter.patient_mpi
> > WHERE encounter_d.encounter_id = encounter.encounter_id
> > AND ((p_l_d.start_date <= encounter_d.from_date OR p_l_d.start_date IS
> > NULL)
> > AND (p_l_d.end_date >= encounter_d.from_date OR p_l_d.end_date IS NULL))
> > ORDER BY encounter.encounter_id, encounter_d.encounter_d_id
>
> >
> > With the ORDER BY
> > NOTICE: adding missing FROM-clause entry for table "encounter"
> > NOTICE: adding missing FROM-clause entry for table "encounter_d"
> > ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the
> > result columns
> >
> I suppose this is because the columns in the except are the same that
> the ones in the main select and the order by get confused.
>
> i'm redirecting to hackers to know if this is a known bug or there is
> something wrong in the select? i don't see anything wrong!!

No, it's because to the order by, the column names are the ones given by
the part after the period of the first select. If you do a plain select
UNION select with no order by, you'll see the title for the columns is
taken from the first select list column names.

So, the order by needs to be "order by encounter_id, encounter_d_id