Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id CAA67883 for ; Mon, 28 Feb 2000 02:38:31 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id CAA21252; Mon, 28 Feb 2000 02:38:03 -0500 (EST) To: vikrant@chemquick.com cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] SQL query problem In-reply-to: <200002280530.NAA12451@chemquick.com> References: <200002280530.NAA12451@chemquick.com> Comments: In-reply-to "Vikrant Rathore" message dated "Mon, 28 Feb 2000 13:40:21 +0800" Date: Mon, 28 Feb 2000 02:38:03 -0500 Message-ID: <21249.951723483@sss.pgh.pa.us> From: Tom Lane "Vikrant Rathore" writes: > I have been able to run the following query successfully on postgresl- > 6.3.2 but unable to run the same in Postgresql-6.5.2. > select > a.offer_id,a.comp_id,prod_name,date(offer_date),time(offer_date), > ... > group by a.offer_id > ... If you group by a.offer_id, then your select list can't refer to any other columns except as aggregate-function arguments. For example, it would make sense to ask for min(a.comp_id) to get the smallest comp_id out of each group of rows with the same offer_id. But that group of rows doesn't necessarily all have the same comp_id, so you can't expect to ask for unvarnished comp_id and get a well- defined answer. Before about v6.5, Postgres was fairly lax about checking for this logical error (which is also a violation of the SQL standard), and would in fact give you back some randomly-chosen comp_id value. Recent versions detect the error, however. regards, tom lane