How find TOP/MAX value for each id via SQL query in Oracle?

Question

How do I use a query to find the highest value for each identifier (not unique)? My table:

id      date      repeat_cycle
8    30.07.2020      0
4    28.04.2020      1
4    28.04.2020      0
15   01.01.2020      9
15   24.12.2019      8
15   23.12.2019      7
1    20.12.2019      5
15   19.12.2019      6
1    19.12.2019      4

And I want for each id the maximum (the highest number it has in repeat_cycle). My SQL query is wrong and i don't know why. Someone would advise how to fix it or another query.

SELECT * FROM (
        SELECT 
         id,
         date,
         repeat_cycle
        FROM table t1
           order by repeat_cycle desc
        ) t1
and rownum=1;

Answer 1

You can use analytic functions:

select *
from (
    select 
        t.*, 
        row_number() over(partition by id order by repeat_cycle desc) rn
    from mytable t
) t
where rn = 1

Alternatively, if there are only three columns in the table, the keep syntax might be appropriate:

select
    id,
    max(date) keep(dense_rank first order by repeat_cycle desc) date,
    max(repeat_cycle) repeat_cycle
from mytable