Nisanth Nisanth - 3 months ago 6
MySQL Question

how to select records based on recent date in mysql

I have 3 tables like below :

hr_emp_job_compensation:

id date fkEmp_id basic_wage part_hours part_amt

1 04-01-2016 1 4500 35 120
2 04-01-2016 3 3800 30 150
3 08-01-2016 3 3200 30 100


hr_emp_job_info:

id fkEmp_id

1 1
2 3


hr_emp_info:

id employee_id first_name

1 001 Ram
2 002 Lak
3 003 jai
4 004 shiva


I want to select records from table 1 , based on the column Date value is higher.

I Try the following query :

SELECT t1.fkEmp_id,max(t1.date),max(t1.id) as uid,t1.part_hours,t1.part_amt, t3.first_name, t3.employee_id
FROM `hr_emp_job_compensation` as t1
inner join `hr_emp_job_info` as t2 on t1.fkEmp_id = t2.fkEmp_id
left join `hr_emp_info` as t3 on t3.id = t1.fkEmp_id
group by t1.fkEmp_id


But the result is look like below :

fkEmp_id max(t1.date) uid part_hours part_amt first_name employee_id
1 2016-01-04 1 35 120 Ram 001

3 2016-01-08 3 30 150 Jai 003


Here the part_hours and part_amt columns are fetched from the id 2. How to change the query.

Answer

No need to add MAX() for the dateand id. You can handle the MAX(date) in the WHERE clause.

SELECT  t1.fkEmp_id, t1.date as `date`, t1.id as uid,
        t1.part_hours, t1.part_amt, 
        t3.first_name, t3.employee_id 
FROM `hr_emp_job_compensation` as t1 
INNER JOIN `hr_emp_job_info` as t2 on t2.fkEmp_id = t1.fkEmp_id
LEFT JOIN `hr_emp_info` as t3 on t3.id = t1.fkEmp_id 
WHERE t1.`date`= ( SELECT MAX(`date`) 
                   FROM `hr_emp_job_compensation` 
                   WHERE fkEmp_id = t1.fkEmp_id);

Please find the Working Demo