select multiple columns with aggregations

Question

I have a table with some fields (time, text, type). I want to build a query to return for every type the text that was introduced with the maximum value of time. Oracle has some restrictions and it is not simple to build the query without some tricks.

I am struggling to get the right query, can anyone help me with it?

TIME TEXT TYPE
--------------------------
03.05.2020 AA 2
02.04.2020 BB 2
01.04.2020 CC 1

I want a query that returns

03.05.2020 AA 2
01.04.2020 CC 1

score 1 · Answer 1 · answered Mar 05 '20 at 08:20

1

First is to get the max(TIME) per type, then join it to your tableA to get other fields (TEXT).

select * from tableA t
inner join 
     (select max(TIME) mt, type from tableA group by type) t1 on t1.mt=t.mt and t.type= t1.type

answered Mar 05 '20 at 08:20

Ed Bangga

12,879
4
16
30

score 1 · Answer 2 · answered Mar 05 '20 at 08:20

1

You can use row_number (or dense_rank):

select
    t.*
from (
    select
        t.*,
        row_number() over(partition by type_column order by time_column desc) rn
    from tbl t
) t
where t.rn = 1

answered Mar 05 '20 at 08:20

User9123

1,643
1
8
21

`dense_rank()` is a better option than `row_number()` since `dense_rank()` returns all the tie records for time for each type whenever multiple maximum time values exist for any. – Barbaros Özhan Mar 05 '20 at 10:11

score 1 · Accepted Answer · answered Mar 05 '20 at 09:02

One option would be using DENSE_RANK, FIRST and LAST Analytic Functions as

MAX(time) KEEP (DENSE_RANK LAST ORDER BY time ) OVER( PARTITION BY type )

WITH t2 AS
(
SELECT t.*, MAX(time) KEEP (DENSE_RANK LAST ORDER BY time ) OVER( PARTITION BY type ) 
            AS highest
  FROM t
)
SELECT time, text, type
  FROM t2
 WHERE highest = time

Through this method all the ties(repeating values for time for each type group) are listed.

Demo

select multiple columns with aggregations

3 Answers3