Thread: 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
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 ----1033 (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 >
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
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
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 >> >
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)