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
>>
>
