CODEWITHSUNDEEP
phpmysqlsqlgroup-bymax
Marcel Grüger
Marcel Grüger

Reputation: 934

MySql Query with INNER JOIN, GROUP BY and Max

I have two tables, one that looks like this:

ID, Datetime, User_ID, Location and Status // the rest is not relevant

And the other looks like this:

 ID, Lastname // the rest is not relevant

Now I only want to get the entry of the first table with the highest Datetime per User_ID and ask the other table the lastname of the User_ID. Simple...

I tried it this way (whick looks like the most promising but is false nontheless):

SELECT w.Datetime, w.User_ID, w.Status, e.Lastname
FROM worktimes AS w
INNER JOIN employees AS e 
ON w.User_ID=e.ID 
RIGHT JOIN (SELECT max(Datetime) AS Datetime, User_ID
  FROM worktimes 
  WHERE Datetime>1467583200 AND Location='16' 
  GROUP BY User_ID
  ORDER BY Datetime DESC
) AS v
ON v.User_ID=w.User_ID
GROUP BY w.User_ID 
ORDER BY e.Nachname;

Could someone give me a hint please? I'm really stuck at this for a while now and now i begin to get some knots in my brain... :(

Upvotes: 0

Views: 218

Answers (2)

Jacobian
Jacobian

Reputation: 10812

What you are asking about is known as correlated subqueries. In standard SQL it can be implemented using APPLY and LATERAL constructs. Unfortunatelly, not all RDBMS support these elegant solutions. For example, MSSQL, recent versions of Oracle and Postgresql have these constructs, but MySQL does not. IMHO, it is a real pain for MySQL users, because in recent years MySQL started to lean towards standard, but in some strange manner - by default it switches off its non-standard hacks, but does not implement standard counterparts. For example, your own query presented in your question will not work by default in recent versions of MySQL, because sorting in subqueries is not supported any more and to make it work you have to use some nasty hack - add LIMIT some_really_big_number to the subquery.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You are very close, actually:

SELECT w.Datetime, w.User_ID, w.Status, e.Lastname
FROM worktimes w INNER JOIN
     employees e 
     ON w.User_ID = e.ID LEFT JOIN
     (SELECT max(Datetime) AS Datetime, User_ID
      FROM worktimes 
      WHERE Datetime > 1467583200 AND Location = '16' 
      GROUP BY User_ID
     ) ww
     ON ww.User_ID = w.User_ID AND w.DateTime = ww.DateTime
ORDER BY e.Nachname;

Notes:

  • You do need to join on the DateTime value.
  • The RIGHT JOIN is unnecessary. I replaced it with a LEFT JOIN, but I'm not sure that is what you want either. You might start with an INNER JOIN to see if that produces what you want.
  • Do not use ORDER BY in subqueries in most circumstances.
  • You do not need the GROUP BY in the outer query

Upvotes: 2

Related Questions