From: Pavel Stehule <pavel.stehule@gmail.com>
To: Feixiong Li <feixiongli@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to max() make null as biggest value?
Date: Tue, 20 Apr 2010 19:02:21 +0200
Message-ID: <r2y162867791004201002x50843917y3d1f1293db7451e0@mail.gmail.com> (raw)
In-Reply-To: <s2z162867791004200817j80b0a976u9979bfc7e7d73f25@mail.gmail.com>
References: <2A8BBCA2-814C-4E8D-BCE1-11FA2F8D120E@gmail.com>
<s2z162867791004200817j80b0a976u9979bfc7e7d73f25@mail.gmail.com>
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)
limit 1
$$ 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
>>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pavel.stehule@gmail.com, feixiongli@gmail.com
Subject: Re: How to max() make null as biggest value?
In-Reply-To: <r2y162867791004201002x50843917y3d1f1293db7451e0@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox