Skip to content
Advertisement

SQL query to fetch distinct values from same table

Let’s say I have this table: employeetable:

empid joindate location HRupdatedate Changes
1 2002-01-05 Delhi 2021-03-01 New
2 2009-09-09 Mumbai 2021-03-05 New
1 2010-06-27 Hyderabad 2021-03-03 Transfer
2 2015-11-02 Delhi 2021-03-06 Transfer
3 2020-01-01 Mumbai 2021-03-06 New
4 2007-07-30 Delhi 2021-03-04 New

I want to get data from this table where empid in (1,2,4). That will return:

empid joindate location HRupdatedate Changes
1 2002-01-05 Delhi 2021-03-01 New
2 2009-09-09 Mumbai 2021-03-05 New
1 2010-06-27 Hyderabad 2021-03-03 Transfer
2 2015-11-02 Delhi 2021-03-06 Transfer
4 2007-07-30 Delhi 2021-03-04 New

But I only need the latest record without the duplicate entries:

empid joindate location HRupdatedate Changes
1 2010-06-27 Hyderabad 2021-03-03 Transfer
2 2015-11-02 Delhi 2021-03-06 Transfer
4 2007-07-30 Delhi 2021-03-04 New

How to achieve this?

Advertisement

Answer

You can easily achieve this by using WHERE clause.

select * from  
employeetable et1
where 
    joindate = (
        select max(joindate)
            from employeetable et2 
            where et1.empid = et2.empid
    ) 
    and 
    empid in (1,2, 4);

Or for more precise results

select * from  
employeetable et1
where 
    joindate = (
        select max(joindate)
            from employeetable et2 
            where et1.empid = et2.empid
    ) 
    and 
    HRupdatedate = (
        select max(HRupdatedate)
            from employeetable et2 where et1.empid = et2.empid
    )
    and 
    empid in (1,2, 4);

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement