I am trying to use a query to return the count from rows such that the date of the rows matches the maximum date for that column in the table.

Oracle SQL: version 11.2:

The following syntax would seem to be correct (to me), and it compiles and runs. However, instead of returning JUST the count for the maximum, it returns several counts more or less like the "HAIVNG" clause wasn’t there.

Select ourDate, Count(1) as OUR_COUNT
from schema1.table1
group by ourDate
 HAVING ourDate = max(ourDate) ; 

How can this be fixed, please?

>Solution :

I don’t know if I understand what you want. Try this:

Select x.ourDate, Count(1) as OUR_COUNT
from schema1.table1 x
 where x.ourDate = (select max(y.ourDate) from schema1.table1 y)
group by x.ourDate