Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 16800634B50 for ; Tue, 20 Apr 2010 13:23:34 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 45952-03-7 for ; Tue, 20 Apr 2010 16:23:14 +0000 (UTC) X-Greylist: delayed 00:15:02.758464 by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id B8311635F46 for ; Tue, 20 Apr 2010 12:45:53 -0300 (ADT) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id GK8FHGQM; Tue, 20 Apr 2010 16:30:49 +0100 Message-ID: <2B9CA716C0F54A07B23E624B5424D852@marktestcr.marktest.pt> From: "Oliveiros" To: "Feixiong Li" , References: <2A8BBCA2-814C-4E8D-BCE1-11FA2F8D120E@gmail.com> Subject: Re: How to max() make null as biggest value? Date: Tue, 20 Apr 2010 16:30:49 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.128 tagged_above=-10 required=5 tests=AWL=-0.117, BAYES_20=-0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201004/67 X-Sequence-Number: 33994 Howdy, Feixiong. I dunno if this results out of the box, but try something like this SELECT CASE WHEN (COUNT(*) <> COUNT("YourColumnName")) THEN NULL ELSE MAX("YourColumnName") END AS "...." FROM t_your_table; I don't have an example table to test so this is "air code", but if you use count on a column with nulls the return value is different from count(*) HTH Best, Oliveiros Cristina ----- Original Message ----- From: "Feixiong Li" To: Sent: Wednesday, April 14, 2010 5:33 AM Subject: [SQL] How to max() make null as biggest value? > Hi , guys , > > I am newbie for sql, I have a problem when using max() function, I need > get null when there are null in the value list, or return the largest > value as usual, who can do this? > > i.e. max([1,2,3,4,5]) => 5 > max([1,2,3,4,5,null]) => null > > thanks in advance! > > Feixiong > feixiongli@gmail.com > > > > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql