Select a MAX on Date column not working with empty table

i am running the following query on one of my table which having a date column.

开发者_StackOverflow中文版select max(date) from mydate

Problem is, when table is empty, then also its returning a row. I am using Oracle 11 .

Any suggestion?

This is a valid behavior: aggregate functions usually return a NULL being applied to an empty set, and COUNT(*) returns 0.

If you don't want a row if the table is empty, use this:

SELECT  MAX(date)
FROM    mydate
HAVING  COUNT(*) > 0

Actually it is working. It is just not working the way you think it should. Always returning a result is the documented behaviour for aggregating functions.

SQL> select ts from t23;

TS
---------
25-APR-11
26-APR-11

SQL> select max(ts) from t23
  2  /

MAX(TS)
---------
26-APR-11

SQL> delete from t23
  2  /

2 rows deleted.

SQL> select max(ts) from t23
  2  /

MAX(TS)
---------


SQL>

However, if you really want an empty set and handle it accordingly you'll have to do something like this:

SQL> select max_ts from
  2      ( select max(ts) as max_ts from t23 )
  3  where max_ts is not null
  4  /

no rows selected

SQL>

Hmm, Quassnoi's solution is neater (less typing):

SQL> select max(ts) from t23
  2  having count(*) > 0
  3  /

no rows selected

SQL>

As Quassnoi points out, my solution will return an empty set even if the table has rows, provided no instance of the column TS is populated. Q's solution would return a row with a NULL result under the same circumstances. It depends on the business requirement as to which result is the right one.

If you want to get ride of the NULL try:

CREATE TABLE my_date (
  the_date DATE
);

SELECT MAX(the_date) FROM my_date HAVING MAX(the_date) IS NOT NULL;

Cheers,

Likely the query is returning one row with a null value. That's the specified behavior of MAX. This link provides a decent overview of how NULL values are handled in SQL.

select @maxPK = max(id) from #dd having count(id) > 0

this will work.