Re: [HACKERS] [GENERAL] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [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 ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [GENERAL] Issue with adding ORDER BY to EXCEPT.
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. pgpj93Gz0htfl.pgp Description: PGP signature
