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

#1

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 (AT) gmail (DOT) com>
To: <pgsql-sql (AT) postgresql (DOT) org>
Sent: Wednesday, April 14, 2010 5:33 AM
Subject: [SQL] How to max() make null as biggest value?

Quote:

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 (AT) gmail (DOT) com

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#2

Feixiong Li <feixiongli (AT) gmail (DOT) com> wrote:

Quote:

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:

Quote:

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:

Quote:

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

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#3

On 04/14/10 08:33, Feixiong Li wrote:

Quote:

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)

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

mailing.database.pgsql-sql mailing.database.pgsql-sql

Re: [SQL] How to max() make null as biggest value? - 04-20-2010 , 10:30 AM

Re: [SQL] How to max() make null as biggest value? - 04-20-2010 , 11:47 AM

Re: [SQL] How to max() make null as biggest value? - 04-21-2010 , 02:43 AM