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);