CODEWITHSUNDEEP
phpmysql
Nisanth
Nisanth

Reputation: 333

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.

Upvotes: 1

Views: 88

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 1

Related Questions