CODEWITHSUNDEEP
sqloracle11g
Anurag Chand
Anurag Chand

Reputation: 19

Latest date in SQL

Table Employee

ID   Name  Update_date Active Phone_no
1    Dave  02-07-14     Y      99999945
1    Dave  19-12-16     Y      88888888
2    Mike  12-11-17     Y      234234567
2    Mike  12-11-14     Y      345435343

What I need is:Select * from employee where id in (1,2); Above query will give me above table as it is. But what I need is

ID   Name  Update_date Active Phone_no
1    Dave  19-12-16     Y      88888888
2    Mike  12-11-17     Y      234234567

For every ID it search it should take latest date.

Upvotes: 0

Views: 55

Answers (2)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Chedk this .

 select * from 

 (    Select  ID,Name,row_number() over (partition by ID order by Update_date desc ) as row ,Active ,Phone_no ,
        Update_date 

        from employee  
)  a where id in (1,2) and row='1'

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could use tuple and a subselect with in clause and group by 

select * from my_table 
where ( id, Update_date) in (  select  id, max(update_date)
                               from my_table
                               group by id)

Upvotes: 1

Related Questions