Mail-Followup-To: pgsql-sql@postgresql.org
To: pgsql-sql@postgresql.org
connect(): No such file or directory
From: Tim Landscheidt <tim@tim-landscheidt.de>
Subject: Re: How to max() make  null as biggest value?
Date: Tue, 20 Apr 2010 16:47:06 +0000
Organization: <URI:http://www.tim-landscheidt.de/>
Lines: 43
Message-ID: <m37ho2oyut.fsf@passepartout.tim-landscheidt.de>
References: <2A8BBCA2-814C-4E8D-BCE1-11FA2F8D120E@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Complaints-To: usenet@dough.gmane.org
X-Gmane-NNTP-Posting-Host: e177121136.adsl.alicedsl.de
Mail-Copies-To: never
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:pnY2hyUXeOfDRA45ZuRGJV+s4Ro=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.162 tagged_above=-10 required=5 tests=AWL=-0.252,
	BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201004/71
X-Sequence-Number: 33998

Feixiong Li <feixiongli@gmail.com> wrote:

> 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

You can cheat a bit:

| tim=# CREATE TABLE MaxTest (i INT);
| CREATE TABLE
| tim=# INSERT INTO MaxTest (i) VALUES (1), (2), (3), (4), (5), (NULL);
| INSERT 0 6
| tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect;
|   maxi
| --------
|  (null)
| (1 Zeile)

| tim=# DELETE FROM MaxTest WHERE i IS NULL;
| DELETE 1
| tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect;
|  maxi
| ------
|     5
| (1 Zeile)
| tim=#

You can also use FIRST_VALUE() (or LAST_VALUE()) if that's
more to your liking. Be careful though with empty sets:

| tim=# DELETE FROM MaxTest;
| DELETE 5
| tim=# SELECT Maxi FROM (SELECT i AS Maxi FROM MaxTest ORDER BY i DESC NULLS FIRST LIMIT 1) AS SubSelect;
|  maxi
| ------
| (0 Zeilen)
| tim=#

Tim