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
