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

2010/4/20 Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> 2010/4/14 Feixiong Li <feixiongli@gmail.com>:
>> Hi , guys ,
>>
>> 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?
>>
>
> max()  returns max value of some column
>
> create table foo(a int);
> insert into foo values(10);
> insert into foo values(33);
>
> postgres=# select * from foo;
>  a
> ----
>  10
>  33
> (2 rows)
>
> Time: 0,524 ms
> postgres=# select max(a) from foo;
>  max
> -----
>  33
> (1 row)
>
> there is function greatest
>
> postgres=# select greatest(1,2,34,2,1);
>  greatest
> ----------
>       34
> (1 row)

sorry, greates_with_null

postgres=#
create or replace function greatest_strict(variadic anyarray)
returns anyelement as $$ select null from unnest($1) g(v) where v is null union all select max(v) from unnest($1) g(v)
limit1 
$$ language sql;CREATE FUNCTION
Time: 232.528 ms
postgres=# select greatest_strict(1,6); greatest_strict
-----------------              6
(1 row)

Time: 3.094 ms
postgres=# select greatest_strict(1,6, null);greatest_strict
-----------------

(1 row)


but you need PostgreSQL 8.4

>
> regards
> Pavel Stehule
>
>> i.e.  max([1,2,3,4,5]) => 5
>> max([1,2,3,4,5,null]) => null
>>
>> thanks in advance!
>>
>> Feixiong
>> feixiongli@gmail.com
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>