How to get a corresponding column while selecting max value?

Question

I have a rather complicated query but for the sake of completeness I will post it as it is:

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS (i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
)
SELECT 
    TYPE, MAX(count_30_days) AS max_incidence
FROM 
    cte
GROUP BY 
    TYPE

What is happening here is that I am applying a rolling window to the Date column and I am counting the occurrence in the last 30 days. From these values I want to have the maximum for each type.

That's what the surrounding statement is for (which is the interesting one).

Of course I have multiple rows before calculating the max value but I want to get the max value of the column count_30_days but also get the corresponding date.

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS (i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
)
SELECT 
    TYPE, MAX(count_30_days) AS max_incidence, DATE
FROM 
    cte
GROUP BY 
    TYPE, DATE

Running this query, I get the following error:

Single-row subquery returns more than one row.

And it makes sense. How do I get the corresponding DATE to the MAX(count_30_days) row?

Answer 1

I think you're looking for this. You want to show an additional column for wich the aggregated value is the MAX. That can be done in multiple ways, but the easiest is to use the KEEP keyword. Here is a simpler example. There is a table with a TYPE column and a DATE column. I want to show the date for the highest type count.

create table sample (
    id                             number generated by default on null as identity 
                                   constraint sample_id_pk primary key,
    type                           varchar2(30 char),
    dt                             date
)
;

-- load data
insert into sample ( type, dt ) values ( 'A',  DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A',  DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A',  DATE'2023-07-01' );
insert into sample ( type, dt ) values ( 'A',  DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'A',  DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'A',  DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'A',  DATE'2023-07-05' );
insert into sample ( type, dt ) values ( 'B',  DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'B',  DATE'2023-07-04' );
insert into sample ( type, dt ) values ( 'B',  DATE'2023-07-05' );
insert into sample ( type, dt ) values ( 'B',  DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'B',  DATE'2023-07-02' );
insert into sample ( type, dt ) values ( 'B',  DATE'2023-07-03' );

WITH cte AS 
(
select type, dt, count(*) as cnt from sample group by type, dt
)
select type, max(cnt) as cnt, max(dt) keep (dense_rank first order by cnt desc) as dt
from cte
group by type;

TYPE                           CNT        DT
------------------------------ ---------- -----------
A                                       3 01-JUL-2023
B                                       2 04-JUL-2023

It shouldn't be too hard to apply this example to your dataset.

This is very well explained (along with the other possibilities to solve this issue) in this video by @Connor McDonald