CODEWITHSUNDEEP
sqloracle-databasegroup-by
michele
michele

Reputation: 26598

SQL Query group and get the more recent state

I have this oracle sql table:

ID | State | Date
x  |  A    | 18-01-01
x  |  B    | 18-01-02
x  |  C    | 18-01-03
y  |  D    | 18-01-04
y  |  E    | 18-01-05
y  |  F    | 18-01-06
z  |  G    | 18-01-07
z  |  H    | 18-01-08
z  |  I    | NULL

I have to write a query that return me the last state for id:

ID | State | Date
X  |  C    | 18-01-03
Y  |  F    | 18-01-06
Z  |  I    | NULL

How can I do ?

Thanks.

Upvotes: 0

Views: 367

Answers (4)

J.JH
J.JH

Reputation: 11

use first_value after sorting date by desc

  select distinct id, first_value(state)    over(partition by state order by date desc)  as 
  state,date from tbl

Upvotes: 0

user5683823
user5683823

Reputation:

The most efficient way is to do it all in a single aggregate query (no subqueries and no analytic functions are needed).

select   id
     ,   min(state) keep (dense_rank last order by dt) as state
     ,   max(dt) as dt
from     <your_table>
group by id
order by id  --  if needed
;

Oracle keywords like date should not be used as column names. I changed that to dt.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would simply use aggregation using keep:

select id,
       max(state) keep (dense_rank first order by date desc) as state,
       max(date) as date
from t
group by id;

Upvotes: 0

Mayank Porwal
Mayank Porwal

Reputation: 34046

Use ROW_NUMBER()

Select id,state,date
FROM
(
select id,
       ROW_NUMBER() OVER(partition by id order by date desc) as state,
       date 
from mytable
)a
WHERE state=1;

Upvotes: 1

Related Questions