Обсуждение: Re: [GENERAL] Issue with adding ORDER BY to EXCEPT.
> 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!! -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
On Tue, Jun 07, 2005 at 12:42:47PM -0500, Scott Marlowe wrote: > On Tue, 2005-06-07 at 12:16, Jaime Casanova wrote: > > 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 Or even easier: order by 1, 2; Then you don't even need to know the column names... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
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