Thread: SQL query problem
Dear Colleagues, 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. can anyone tell me the error and why it is running fine in 6.3.2 but not in 6.5.2.: select a.offer_id,a.comp_id,prod_name,date(offer_date),ti me(offer_date), clicks,bidcount,ship_price, ship_price_currency,ship_currency_unit, date_part('day',age(expiry_date,'now')), date_part('hour',age(expiry_date,'now')), date_part('min',age(expiry_date,'now')), offer_type from offer a, product b,enquiry c where expiry_date>='now' and b.prod_id = a.prod_id and a.offer_id=c.offer_orig and bidcount<>0 and offer_type='sell' and upper(transaction_complete)='N' group by a.offer_id order by prod_name; Thanks in advance for the help. With Best Regards, Vicky Alpha Group (Hong Kong) Ltd. 15/B Wah Kit Commercial Centre 300-302, Des Voeux Road Central Sheung Wan Hong Kong Phone : (852) 28507827 FAX : (852) 28504229 Email : vikrant@alphahkg.com
"Vikrant Rathore" <vikrant@chemquick.com> 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