MySQL GroupBy OrderBy

Question

The first question you are probably asking is how many question order groups are there on SO? A lot of. None of them match my specific circumstances. Here is my next SQL query.

The goal is to get the latest media for each team in $teamArr (a set of unique identifiers).

The problem that I am facing is that she will not receive the latest media for everyone, but it is based on the order of the group, according to which the media’s time is independent of the team.

Here is the MySQL command (with the PHP sprintf() command for ease of use with command identifiers).

 sprintf("SELECT M.*, T.name, T.has_image FROM media AS M JOIN teams AS T ON T.team_uid = M.team_uid WHERE M.team_uid IN (%s) GROUP BY T.team_uid ORDER BY M.time DESC", implode(", ", $teamArr)); 

Scenario:

team A has a media element 3 hours ago, team B has 2 elements 6 hours ago and 2 days ago, and team C has a media element 9 hours ago.

If the selection order is A, C, B, then the media elements will look like this:

• TeamA 3 hours ago Media Element
• TeamC 9 hours ago media element
• TeamB 2 days ago Media Element

There will probably be some silly remarks saying

Just order by team mannequin name! Such a simple problem. (this answer will get about 4 votes)

But that clearly doesn't help me at all. I cannot know which teams will have the optimal order (if any natural or unnatural order will work even with more than three cases). Is there a way I can do this in 1 SQL query. I heard that MySQL in a loop is a bad solution, so the reason I want this to work in 1 SQL call.

Answer 1

This type of request is usually marked as "largest-n-per-group" for future reference.

You want the maximum time for each team and the corresponding columns, right?

A typical way to solve this problem is the LEFT JOIN table that you want to get the most for yourself (in your case media ):

 SELECT M.*, T.name, T.has_image FROM media AS M LEFT JOIN media AS M2 -- new: join media to itself ON M.team_uid = M2.team_uid -- within each team_uid (GROUP BY var) AND M.time < M2.time -- and a sort on time (ORDER BY var) JOIN teams AS T -- same as before. ON T.team_uid = M.team_uid WHERE M.team_uid IN (%s) AND M2.time IS NULL -- new: this selects the MAX M.time for each team. 

Changes:

• LEFT JOIN media for itself inside each team_uid variable ( GROUP BY variable) and such that M.time < M2.time (variable that requires MAX).
• since this is a LEFT JOIN , if there is M.time for which there is no longer M2.time (for the same command), then M2.time will be NULL. It is when M.time is the MAX time for this team.
• add M2.time IS NULL to the WHERE (to enforce the above)
• remove GROUP BY , ORDER BY (considered in the join condition).