From: Tim Landscheidt <tim@tim-landscheidt.de>
To: pgsql-sql@postgresql.org
Subject: Re: How to max() make null as biggest value?
Date: Tue, 20 Apr 2010 16:47:06 +0000
Message-ID: <m37ho2oyut.fsf@passepartout.tim-landscheidt.de> (raw)
References: <2A8BBCA2-814C-4E8D-BCE1-11FA2F8D120E@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tim@tim-landscheidt.de
Subject: Re: How to max() make null as biggest value?
In-Reply-To: <m37ho2oyut.fsf@passepartout.tim-landscheidt.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox