CODEWITHSUNDEEP
oracle-databaseoracle11gaggregateaggregate-functionshaving
JosephDoggie
JosephDoggie

Reputation: 1594

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

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?

Upvotes: 0

Views: 466

Answers (4)

Chikajah
Chikajah

Reputation: 11

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;

Upvotes: 1

MT0
MT0

Reputation: 167982

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

db<>fiddle here

Upvotes: 2

OracleDev
OracleDev

Reputation: 535

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

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;

Upvotes: 1

Related Questions