query with sql max function

table view with 4 records

Would the following query work if I wanted to select the 2nd and 3rd records?

select driver_name, driver_city, driver_email, max(due_date), max(completion_date)
from TableX
group by driver_name,driver_city,driver_email

Answer

As your date values are not in well formatted, you need to first convert your values into DATE and then apply other logic. Please check my updated script now-

Issues:

1. You value in completion_date column is string. as a result, MAX was returning the value considering STRING, not DATE.
2. You have no YEAR value in the completion_date column. As a result, I just added ‘2020’ to the value so that DATE value generated properly. At the end, the added year has no impact on the output. And this is considering all DTAEs are from the single (any) YEAR only.
3. But if you have YEAR value in some other column, avoid adding static ‘2020’ and use the value from that column

Check DEMO HERE

    SELECT A.*
FROM TableX A
INNER JOIN (
    SELECT driver_email, 
    MAX(STR_TO_DATE(
        CONCAT(
        REPLACE(
            REPLACE(REPLACE(completion_date,'rd ','-'),
            'st ',
            '-'
        ),
        'th ',
        '-'
    ),'-2020') ,'%d-%b-%Y')) completion_date
    FROM TableX 
    GROUP BY driver_email
) B ON A.driver_email = B.driver_email
AND STR_TO_DATE(
        CONCAT(
        REPLACE(
            REPLACE(REPLACE(A.completion_date,'rd ','-'),
            'st ',
            '-'
        ),
        'th ',
        '-'
    ),'-2020') ,'%d-%b-%Y') = B.completion_date