How to write a query for this two tables [closed]

EMPLOYEE(id,emp_name)
1, John
2, Michael
3, Philip

===================

RANK(id,emp_id,date,rank)
1,1,1583413078445,SE
2,1,1583413087085,SSE
3,1,1583413092169,PM
4,2,1583413131802,SE
5,3,1583413536655,SE

I want to show

employee id, employee name and latest Rank (whose id is maximum for emp_id in rank table or whose longdate is maximum for emp_id in rank table) from Rank table

id,name,Rank
1, John, PM
2, Michael, SE
3, Philip, SE

I am using java and my database is mysql and sqlite (in case offline) I tried some way but could not aggregate the queries.

Answer

SELECT RK.emp_id, EP.emp_name, RK.rank
FROM RANK RK
LEFT JOIN employee EP ON RK.emp_id=EP.id
GROUP BY RK.emp_id
HAVING MAX(RK.id);

Leave a Reply

Your email address will not be published. Required fields are marked *