If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

This would give me a somewhat desired result only the shift_id is incorrect, seems like it's selecting the most recently saved shift_id and not the shift_id that is of the same row as the latest effectivity_date of each user_id.

You see? You have *NOT SPECIFIED* which shift_id you want, so MySQL feels free to pick ANY ONE THAT IS AVAILABLE. Usually, it will simply pick the first one it finds (but that's not universal), so that's why you get back

Regarding having the same effectivity date, I think I'll just change column type to timestamp so that there'll never be dates of the same value, what do you think?

Or to DATETIME and record the value to the second, yes. That's certainly viable if you can ensure that those values won't ever get duplicates.

Code:

( SELECT user_id, MAX(effectivity_date) AS maxDate
FROM user_shift_schedule
GROUP BY user_id ) AS M

What does this do exactly? Is it making a new temporary table?

In essence, yes. It doesn't really create a temp table, but it creates a set of records in memory that satisfy the SELECT. So yes, it's easier to think of it as creating a temp table or, perhaps better, a pseudo-table. (Temp tables in MySQL are also possible, but they last so long as the same connection is open--meaning you could use the same temp table in multiple SELECTs or UPDATEs; in this above code, the pseudo-table lasts only so long as the query is executing.)