Select a MAX on Date column not working with empty table

Question

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

select max(date) from mydate

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

Any suggestion?

Answer 1

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