Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 1C3FD2E00BF for ; Fri, 14 Dec 2007 13:24:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 89162-02 for ; Fri, 14 Dec 2007 13:24:16 -0400 (AST) Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.233]) by postgresql.org (Postfix) with ESMTP id E444A2E0075 for ; Fri, 14 Dec 2007 13:24:15 -0400 (AST) Received: by nz-out-0506.google.com with SMTP id l8so605728nzf.28 for ; Fri, 14 Dec 2007 09:24:15 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=YckNJCW7ox1AKp8q7el2WQhrVd0E+OqIbeIzykN2HoU=; b=U0PaV7cJgPliLxRwGgm1xMN6wQmOYwenAoqHznWFgjYew9ghxp49ePoP6Z5e3kIfGe90NgSOm8xxIGaE2N3OexGMb0FjnGBenCTYzJ295JzY/gmNYQ9rFQAyYcRKWlJftXhyM2+N7OUuFJE7U9zL6e6otRbKg4mJqQPUko4uuuo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=WFi1YNLCvkRxYn250IGdTVj5KTOVwecLaxXF+3Yc9t4eJwDnOMzVYVkl2Gc850XjpCvHlZK0KYbgTty7Dfe7DxVyeVzh1KUEII3bGqugSRPsDhPqM6PuWAN60tduwz5fkyi9kFjwn1ruvqmzrOD1JTVCMOjUef7DZAuGWJPWDjw= Received: by 10.142.135.9 with SMTP id i9mr672086wfd.211.1197653054682; Fri, 14 Dec 2007 09:24:14 -0800 (PST) Received: by 10.142.165.8 with HTTP; Fri, 14 Dec 2007 09:24:14 -0800 (PST) Message-ID: Date: Fri, 14 Dec 2007 11:24:14 -0600 From: "Scott Marlowe" To: "Gavin Baumanis" Subject: Re: SELECT MAX returns wrong value Cc: pgsql-sql@postgresql.org In-Reply-To: <4761BBA7.7030800@eclinic.com.au> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4761BBA7.7030800@eclinic.com.au> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/87 X-Sequence-Number: 29779 On Dec 13, 2007 5:09 PM, Gavin Baumanis wrote: > Hi Everyone, > > Sorry if I am missing something obvious but I think I have found a bug. > If I perform the following SQL > > SELECT MAX(column) FROM table WHERE expression > > and there is no match, Postgres returns a record count of 1. > There is no value in max, it is NULL. that's because you got one record back. A null one, but a record none the less. The standard way of doing this is: select count(column) from table where expression. since null columns don't get counted, it will return zero if they're all null.