Feixiong Li <feixion...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
