Filter rows using on conditions twice the same column

Question

I have the next query:

        select CHANNEL , my_date 
        from table_1 d
        where source_data = 'test_5'
          and my_date < to_date('27/09/2020','DD/MM/YYYY')
          and customer_ID = :param_customer_ID
        order by d.my_date asc;

That will show the next result:

My need is. have the last vale filter for the last my_date, grouping by channel. My result for this example must look like this:

Just the two rows.

I tried with:

select CHANNEL , my_date 
from table_1 d
where source_data = 'test_5'
  and (my_date < to_date('27/09/2020','DD/MM/YYYY') and my_date = max(my_date))
  and customer_ID = :param_customer_ID
group by CHANNEL, my_date 
order by d.my_date asc;

but nothing, it doesn't work, and give me error

ORA-00934: función de grupo no permitida aquí
00934. 00000 -  "group function is not allowed here"
*Cause:    
*Action:
Error en la línea: 138, columna: 30

what should i do?

Regards

Answer 1

In Oracle, you can use aggregation:

select channel, max(my_date)
from table_1 d
where source_data = 'test_5' and
      my_date < date '2020-09-27' and
      customer_ID = :param_customer_ID
group by channel;

If there are more columns that you want, then use row_number():

select channel, my_date
from (select d.*,
             row_number() over (partition by channel order by my_date desc) as seqnum
      from table_1 d
      where source_data = 'test_5' and
            my_date < date '2020-09-27' and
            customer_ID = :param_customer_ID
     ) d
where seqnum = 1;