Return row with the latest date

Question

My data loooks like this,

  employeeSalaryId  employeeId  title   value       currency    effectiveFrom
       10            1000      Salary   22750.00     USD            2021-01-01
       4             1000      Salary   19500.00     USD            2020-09-23

I want to return records based on the latest effectiveFrom date. So for the above sample data for employeeId 1000 the first row would be the requried result.

SELECT 
    `es`.`employeeSalaryId`,
    `es`.`employeeId`,
    `es`.`totalSalary`,
    `es`.`currency`,
    MAX(DATE(`es`.`effectiveFrom`)) AS `MAXeffectiveFrom`
FROM
    `Employee_Salary` `es`
WHERE
    `es`.`employeeId` = 1000;

I am getting the result below which is not correct,

 employeeSalaryId   employeeId  totalSalary   currency  MAXeffectiveFrom
   4                   1000       19500.00     USD       2021-01-01

The date is correct but the totalSalary & employeeSalaryId values shown are for date 2020-09-23.

Desired Result is,

employeeSalaryId  employeeId  title   value       currency    effectiveFrom
       10            1000      Salary   22750.00     USD            2021-01-01

dbfiddle

@Akina fiddle is correct now. On adding *max(date(es.effectiveFrom)) it does not give an output on the fiddle but i am getting the incorrect result as shown on my sql editor — Bisoux, Apr 15 '21 at 10:39
Do you need in [this](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8c5d93ebbf4c41e9045ea318f36439f6)? — Akina, Apr 15 '21 at 10:49

score 3 · Accepted Answer · answered Apr 15 '21 at 10:50

What you are doing is selecting employees data AND maximum date from whole "effectiveForm" column. It means that this date is assigned for every row. If you want to get row with latest date, you should sort it by date descending and select first record.

SELECT 
   `es`.`employeeSalaryId`,
   `es`.`employeeId`,
   `es`.`totalSalary`,
   `es`.`currency`,
   `es`.`effectiveFrom`
FROM
    `tempSalary` `es`
WHERE
   `es`.`employeeId` = 1000
ORDER BY DATE(`es`.`effectiveFrom`) DESC
LIMIT 1;

`DATE()` function usage is obviously excess - the column has DATE datatype already. — Akina, Apr 15 '21 at 10:51

Return row with the latest date

1 Answers1