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

Answer 2

You can use row_number(),

select id, date, repeat_cycle from
(select id, date, repeat_cycle, row_number() over(partition by id order by repeat_cycle desc) as rnk from table_name)
qry where rnk = 1;

Answer 3

You can use max() and group by.

select
    t.id,
    max(t.repeat_cycle)
from
    table t
group by
    t.id

Where table is your real table name.

Answer 4

I would recommend to you not to call your columns using keywords like "date" or your table like "table"

select t1.id, t1.date_c, t1.repeat_cycle      
from table_t t1
where (t1.id, t1.repeat_cycle) in (select t2.id, max(t2.repeat_cycle)
                                   from table_t t2
                                   group by t2.id);

Here is a demo