Re: How to max() make null as biggest value?

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