Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

oracle - SQL - Finding the maximum date group by id table

Having a table below, I need to get rows with the maximum date having statut equal 2

REMUN_ID    HISTO_ID   DATE_MAJ                 STATUT
2122        7005       08/27/2014 11:10:23        2
1603        5486       08/27/2014 11:10:21        1
2122        5151       08/27/2014 11:08:36        1
1603        4710       08/27/2014 11:08:32        2 

I need to get the row with the maximum date and group by REMUN_ID the result using this request

select remun_id, max(date_maj)
from histo_statut_remun 
group by remun_id;

Result :

REMUN_ID      DATE_MAJ                 
2122        08/27/2014 11:10:23        
1603        08/27/2014 11:10:21        

I need to adjust the request to get only rows with statut = 2 from this result

My purpose is to get the result below, a subquery of the first one to get only those with statut 2.

REMUN_ID    DATE_MAJ                 
2122        08/27/2014 11:10:23        

PS : if i used the clause where i will get these results :

REMUN_ID     DATE_MAJ                 
2122        08/27/2014 11:10:23        
1603        08/27/2014 11:08:32         

and that's not what i want to get.

Any suggestions? Thank you

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
select remun_id, date_maj
from (
  select r.*, 
         max(date_maj) over (partition by REMUN_ID) as max_date
  from histo_statut_remun r
) 
where date_maj = max_date
  and statut = 2;

SQLFiddle: http://sqlfiddle.com/#!4/7eb75/1


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...