Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e7JLYsm96207 for ; Sat, 19 Aug 2000 17:34:54 -0400 (EDT) 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 RAA06995; Sat, 19 Aug 2000 17:34:25 -0400 (EDT) To: Shaun cc: pgsql-sql@postgresql.org Subject: Re: Failing query... In-reply-to: References: Comments: In-reply-to Shaun message dated "Sat, 19 Aug 2000 15:53:30 +1000" Date: Sat, 19 Aug 2000 17:34:25 -0400 Message-ID: <6992.966720865@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200008/185 Shaun writes: > SELECT a.auction_id, a.user_id, c.other_names, > c.surname, c.email, a.reserve, a.close_time, a.short_desc, > a.long_desc, a.start_time, > (COALESCE((select MAX(bid) from bid where auction_id = a.auction_id > group by auction_id), 0)) as max_bid > FROM Auction a, Customer c > WHERE a.user_id = c.user_id > AND a.auction_id = 754; Sub-selects inside COALESCE don't work :-(. This was just fixed about a week ago --- it will be in 7.1. In the meantime you might try it the other way round: (select COALESCE(MAX(bid), 0) from bid where auction_id = a.auction_id) as max_bid regards, tom lane