Mudders Mudders - 4 months ago 10
SQL Question

Getting max date from a joined table with group by

I've got 3 tables -

entryrecord
,
employee
and
employee_entryrecord
(linking table).

The query I'd like is for it to return the most recent (
max time
)
inout
record for each employee.

employee
id employee
1 John
2 Tom

entryrecord
id created_date inout
1 2016-07-22 16:01:38 1
2 2016-07-22 16:03:22 1
3 2016-07-22 16:05:22 2
4 2016-07-22 16:07:22 2
5 2016-07-22 16:09:22 1


I'd like the follow output
created_date employee inout entryrecordid
2016-07-22 16:09:22 John 1 5
2016-07-22 16:05:22 Tom 2 3


However, in the sqlfiddle below you can see it does not return the correct
inout
and
entryrecordid
values.

I've created a sqlfiddle to view what I've done.

SQL Fiddle

Any help would be great.
Thanks.

Answer

Please give it a try:

SELECT
finalALias.created_date,
E.employee,
finalALias.inout,
finalALias.id AS entryrecordid
FROM employee E

INNER JOIN 
(
    SELECT 
    *
    FROM entryrecord entryR 
    INNER JOIN 
    (
        SELECT
          EER.employeeid,
          MAX(created_date) max_time
        FROM entryrecord ER
        INNER JOIN employee_entryrecord EER ON ER.id = EER.entryrecordid
        GROUP BY EER.employeeid
    ) t 
    ON t.max_time=entryR.created_date
) AS finalALias
ON E.id = finalALias.employeeid
ORDER BY finalALias.created_date DESC;

WORKING DEMO

Just a gentle reminder:

E -> employee

ER -> entryrecord

ERR -> employee_entryrecord

Comments