Select smallest date after group by

Question

Let's start off by saying that I have almost completed my MySQL query, but I just need a final hint towards the answer.

I used the following MySQL query (for reference):

SELECT * FROM
(
    SELECT ll.id AS id, ll.globalId AS globalId, ll.date AS date, ll.serverId AS serverId, ll.gamemodeId AS gamemodeId, ll.mapId AS mapId, origin,
    pjl.id AS pjlid, pjl.globalid AS pjlglobalId, pjl.date AS pjldate, MIN(pjl.date) AS mindate, pjl.serverId AS pjlserverId, pjl.playerId AS pjlplayerId
    FROM (
            (
                SELECT id, globalId, date, serverId, playerId, 'playerjoins' AS origin
                FROM playerjoins pj
                WHERE playerId =976
            )
        UNION ALL 
            (
                SELECT id, globalId, date, serverId, playerId, 'playerleaves' AS origin
                FROM playerleaves pl
                WHERE playerId =976
            )
        ORDER BY date DESC
    )pjl
    JOIN levelsloaded ll ON pjl.date >= ll.date
    GROUP BY ll.id, origin
ORDER BY date DESC) above

This give me the resultset (part of it), that can be found on the following SQL Fiddle: http://sqlfiddle.com/#!2/514b6/1/0

What I want is the following:

• You now see that there are duplicate id's in the resultset, take for the example result with id = 133.
• I want to see the first action that happened after the date in that record (id = 113).
• The date of that record is November, 27 2013 00:00:17+0000.
• Now there are two possible actions that happened directly after that date:
• 1) origin = 'playerjoins' on mindate = November, 28 2013 00:00:18+0000.
• 2) origin = 'playerleaves' on mindate = November, 28 2013 00:00:19+0000.
• Since playerjoins is the one that has first happened, I want that in my final resultset.

So, I hope it is clear with my example: I want to have, for every 2 rows with the same id, the row that has the lowest mindate. I need to be able to see the whole row, so only knowing the lowest mindate per 2 rows does not suffice. I need to know the origin aswell.

EDIT: The answer might be found here, https://stackoverflow.com/a/7745635/2057294 , still investigating it.

Answer 1

The correct query is:

SELECT * 
FROM levelsloaded ll
INNER JOIN
    (SELECT id, MIN(mindate) AS finalmindate
     FROM levelsloaded
     GROUP BY id
) ill
ON ll.id = ill.id AND ll.mindate = ill.finalmindate
ORDER BY date DESC

This does exactly what I described, a more detailed answer can be found in: https://stackoverflow.com/a/7745635/2057294.