Using GROUP BY and MAX() to ignore NULL ?

[Paul Reddin]

Hi,

Is there an efficient way to do the following, I want max() to include NULL
as effectively a minimal value?
e.g
table1
col1 col2
---- ----
A 1
A 10
A NULL

select
col1, MAX(col2) as MAX
from
table1
group by
col1

to get
COL1 MAX
---- ---
A 10

rather than
COL1 MAX
---- ---
A 10
A NULL

Thanks

Paul.

[Ian]

Paul Reddin wrote:

Hi,

Is there an efficient way to do the following, I want max() to include NULL
as effectively a minimal value?
e.g
table1
col1 col2
---- ----
A 1
A 10
A NULL

select
col1, MAX(col2) as MAX
from
table1
group by
col1

to get
COL1 MAX
---- ---
A 10

This is what you will get when you run the query. The aggregate functions
(max, min, sum, avg,etc.) all ignore NULLs in the column. If every value
in the col2 is NULL, then MAX(col2) will return NULL.
Good luck,
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Gert van der Kooij]

In article <1f**************************@posting.google.com >, Paul
Reddin (pa**@abacus.co.uk) says...

Hi,

Is there an efficient way to do the following, I want max() to include NULL
as effectively a minimal value?

I'm not sure this can be called efficient and I didn't optimize it
but it does work :)

create table t2 (COL1 char(2), COL2 int);

INSERT INTO T2
VALUES ('A', 0),('A', 10),('A',null);
with tt (c1,c2) as (
select case
when col2 is null then COL1 concat '_NUL'
else COL1
end,
COL2
from t2 ),
tt2 (c1b, c2b) as (
select c1, max(c2) from tt
group by c1)
select substr(c1b,1,2) as c1b,
case
when substr(c1b,3,4) = '_NUL' then 'NULL'
else char(c2b)
end as c2b
from tt2;
Kind regards, Gert

[Blair Adamache]

Perhaps you could use coalesce with a UNION ALL?

Paul Reddin wrote:

Hi,

Is there an efficient way to do the following, I want max() to include NULL
as effectively a minimal value?
e.g
table1
col1 col2
---- ----
A 1
A 10
A NULL

select
col1, MAX(col2) as MAX
from
table1
group by
col1

to get
COL1 MAX
---- ---
A 10

rather than
COL1 MAX
---- ---
A 10
A NULL

Thanks

Paul.

[Bob Hairgrove]

On 2 Feb 2004 09:08:10 -0800, pa**@abacus.co.uk (Paul Reddin) wrote:

Hi,

Is there an efficient way to do the following, I want max() to include NULL
as effectively a minimal value?
e.g
table1
col1 col2
---- ----
A 1
A 10
A NULL

select
col1, MAX(col2) as MAX
from
table1
group by
col1

to get
COL1 MAX
---- ---
A 10

rather than
COL1 MAX
---- ---
A 10
A NULL

Thanks

Paul.

Am I overlooking something obvious? Or is everyone else?

select
col1, MAX(col2) as MAX
from
table1
where col2 IS NOT NULL
group by
col1;

If you know that at least one value in col2 is greater than zero, you
might get better performance by using "WHERE col2 > 0" instead of
"WHERE col2 IS NOT NULL".

HTH
--
Bob Hairgrove
No**********@Home.com

[miro flasza]

Paul Reddin wrote:

Hi,

Is there an efficient way to do the following, I want max() to include NULL
as effectively a minimal value?

The rank() OLAP function allows you to specify the order of NULLs for
key comparison purposes. In your example you could partition by col1,
order by col2 and request NULLs to collate low:

select t1ranked.col1, t1ranked.col2rank
from
(select col1,
rank() over(partition by col1 order by col2 desc nulls last)
from table1) as t1ranked(col1, col2rank)
where t1ranked.col2rank = 1;

Regards,
Miro

[Paul Reddin]

Sorry for this.

I think I was having a bit of a brainstorm at the end of yesterday.

I was convinced this was what was happening in a the middle of a
complex
view (i.e I extracted out the essence for simplicity)

But, indeed the MAX() does seem to ignore NULLs in the extracted
simple case, so the problem is a little more involved/dependent on
some other factor.

Thanks all.

Paul.