pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to max() make null as biggest value?
6+ messages / 5 participants
[nested] [flat]
* How to max() make null as biggest value?
@ 2010-04-14 04:33 Feixiong Li <feixiongli@gmail.com>
2010-04-20 15:17 ` Re: How to max() make null as biggest value? Pavel Stehule <pavel.stehule@gmail.com>
2010-04-20 15:30 ` Re: How to max() make null as biggest value? Oliveiros <oliveiros.cristina@marktest.pt>
2010-04-20 16:47 ` Re: How to max() make null as biggest value? Tim Landscheidt <tim@tim-landscheidt.de>
2010-04-21 07:43 ` Re: How to max() make null as biggest value? silly sad <sad@bankir.ru>
0 siblings, 4 replies; 6+ messages in thread
From: Feixiong Li @ 2010-04-14 04:33 UTC (permalink / raw)
To: pgsql-sql
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?
i.e. max([1,2,3,4,5]) => 5
max([1,2,3,4,5,null]) => null
thanks in advance!
Feixiong
feixiongli@gmail.com
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to max() make null as biggest value?
2010-04-14 04:33 How to max() make null as biggest value? Feixiong Li <feixiongli@gmail.com>
@ 2010-04-20 15:17 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-04-20 17:02 ` Re: How to max() make null as biggest value? Pavel Stehule <pavel.stehule@gmail.com>
3 siblings, 1 reply; 6+ messages in thread
From: Pavel Stehule @ 2010-04-20 15:17 UTC (permalink / raw)
To: Feixiong Li <feixiongli@gmail.com>; +Cc: pgsql-sql
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)
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to max() make null as biggest value?
2010-04-14 04:33 How to max() make null as biggest value? Feixiong Li <feixiongli@gmail.com>
2010-04-20 15:17 ` Re: How to max() make null as biggest value? Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-04-20 17:02 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2010-04-20 17:02 UTC (permalink / raw)
To: Feixiong Li <feixiongli@gmail.com>; +Cc: pgsql-sql
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
>>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to max() make null as biggest value?
2010-04-14 04:33 How to max() make null as biggest value? Feixiong Li <feixiongli@gmail.com>
@ 2010-04-20 15:30 ` Oliveiros <oliveiros.cristina@marktest.pt>
3 siblings, 0 replies; 6+ messages in thread
From: Oliveiros @ 2010-04-20 15:30 UTC (permalink / raw)
To: Feixiong Li <feixiongli@gmail.com>; pgsql-sql
Howdy, Feixiong.
I dunno if this results out of the box, but try something like this
SELECT CASE WHEN (COUNT(*) <> COUNT("YourColumnName")) THEN NULL ELSE
MAX("YourColumnName") END AS "...."
FROM t_your_table;
I don't have an example table to test so this is "air code", but if you use
count on a column with nulls the return value is different from count(*)
HTH
Best,
Oliveiros Cristina
----- Original Message -----
From: "Feixiong Li" <feixiongli@gmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 14, 2010 5:33 AM
Subject: [SQL] How to max() make null as biggest value?
> 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?
>
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to max() make null as biggest value?
2010-04-14 04:33 How to max() make null as biggest value? Feixiong Li <feixiongli@gmail.com>
@ 2010-04-20 16:47 ` Tim Landscheidt <tim@tim-landscheidt.de>
3 siblings, 0 replies; 6+ messages in thread
From: Tim Landscheidt @ 2010-04-20 16:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to max() make null as biggest value?
2010-04-14 04:33 How to max() make null as biggest value? Feixiong Li <feixiongli@gmail.com>
@ 2010-04-21 07:43 ` silly sad <sad@bankir.ru>
3 siblings, 0 replies; 6+ messages in thread
From: silly sad @ 2010-04-21 07:43 UTC (permalink / raw)
To: pgsql-sql
On 04/14/10 08:33, Feixiong Li wrote:
> 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?
>
> i.e. max([1,2,3,4,5]) => 5
> max([1,2,3,4,5,null]) => null
if u want a function, not an aggregate
then u have the
greatest(...)
except it does not return null on null input
(i was really surprised with this completely perverted behavior
(very unusual for postgres), but it is a fact)
if u want to cheat u may just
coalesce() each input argument
then nullif() a result of the greatest() function
(if only u have enough space in a reference range to room the one
special value instead of null)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-04-21 07:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-14 04:33 How to max() make null as biggest value? Feixiong Li <feixiongli@gmail.com>
2010-04-20 15:17 ` Re: How to max() make null as biggest value? Pavel Stehule <pavel.stehule@gmail.com>
2010-04-20 17:02 ` Re: How to max() make null as biggest value? Pavel Stehule <pavel.stehule@gmail.com>
2010-04-20 15:30 ` Oliveiros <oliveiros.cristina@marktest.pt>
2010-04-20 16:47 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-04-21 07:43 ` silly sad <sad@bankir.ru>
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