Select TOP 2 highest sum based on two columns

Question

I have a table with the following structure:

|     START_DATE        |         END_DATE     | MINUTES | MACHINE |  TYPE  |
23/06/2023 10:05:35      23/06/2023 10:09:35       4         Z       GREEN
23/06/2023 10:10:40      23/06/2023 10:25:40       15        Z       GREEN
23/06/2023 10:25:47      23/06/2023 10:27:47       2         Z       GREEN
23/06/2023 10:27:35      23/06/2023 10:37:35       10        Z       RED
23/06/2023 10:37:35      23/06/2023 10:39:35       2         Z       RED
23/06/2023 10:39:35      23/06/2023 10:45:35       6         X       BLUE
23/06/2023 11:00:00      23/06/2023 11:05:00       5         Y       GREEN
23/06/2023 11:05:00      23/06/2023 11:13:00       8         Y       BLUE

I want to create a query that only shows the Top 2 biggest sum of MINUTES for each MACHINE and TYPE in the same date and hour (from START_DATE column), and also show a column where it would concatenate the TYPE with the sum of the minutes. This would be the expected result:

|     DATE_HOUR       |   MINUTES | MACHINE |  TYPE_MINUTES  |
    23/06/2023_10           21         Z       GREEN (21 minutes)
    23/06/2023 10           12         Z       RED (12 minutes)
    23/06/2023 11           5          Y       GREEN (5 minutes)
    23/06/2023 11           8          Y       BLUE (8 minutes)

Answer 1

You can do:

select *
from (
  select x.*, row_number() over(partition by machine order by m desc) as rn
  from (
    select machine, type, sum(minutes) as m,
      to_char(min(start_date), 'MM/DD/YYYY_HH24') as date_hour
    from t
    group by machine, type
  ) x
) s
where rn <= 2