Oracle SQL -- Finding count of rows that match date maximum in table-CodePudding

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?

CodePudding user response：

You can use:

SELECT MAX(ourDate) AS ourDate,
       COUNT(*) KEEP (DENSE_RANK LAST ORDER BY ourDate) AS ourCount
FROM   schema1.table1

or:

SELECT ourDate,
       COUNT(*) AS our_count
FROM   (
  SELECT ourDate,
         RANK() OVER (ORDER BY ourDate DESC) AS rnk
  FROM   schema1.table1
)
WHERE  rnk = 1
GROUP BY ourDate

Which, for the sample data:

CREATE TABLE table1 (ourDate) AS
SELECT SYSDATE FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
SELECT SYSDATE - 1 FROM DUAL;

Both output:

OURDATE OUR_COUNT

2022-06-28 13:35:01 5

OURDATE	OUR_COUNT
2022-06-28 13:35:01	5

db<>fiddle here

CodePudding user response：

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

CodePudding user response：

One option is to use a subquery which fetches maximum date:

select ourdate, count(*)
from table1
where ourdate = (select max(ourdate) 
                 from table1)
group by ourdate;

Or, a more modern approach (if your database version supports it; 11g doesn't, though):

select ourdate, count(*)
from table1
group by ourdate
order by ourdate desc
fetch first 1 rows only;

CodePudding user response：

You can use this SQL query:

select MAX(ourDate),COUNT(1) as OUR_COUNT
from schema1.table1
where ourDate = (select MAX(ourDate) from schema1.table1)
group by ourDate;