MySQL Refactoring with GROUP BY and HAVING

Question

Hi,

I'm doing some refactoring work on PHP/MySQL code and stumbled across an interesting problem, and as of right now, I can't find a proper solution for it.

I have this table:

CREATE TABLE example
(
    id        INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userID    INT UNSIGNED  NOT NULL,
    timestamp INT UNSIGNED  NOT NULL,
    value     DECIMAL(10,2) NOT NULL
);

What I am trying to do is to get the latest row entry for each distinct userID.

Currently it's done like this:

SELECT DISTINCT userID
FROM example

then with PHP I loop over the result and run this query for each distinct userID

SELECT *
FROM example
WHERE userID = %u
ORDER BY timestamp DESC
LIMIT 1

I tried to combine these queries into one using:

GROUP BY userID
HAVING MAX(timestamp)

but to no avail.

Any suggestions?

Answer 1

SELECT e.id, e.userId, e.timestamp, e.value
FROM example e
WHERE NOT EXISTS
    (SELECT * FROM example e2 
     WHERE e.UserId = e2.UserId and e2.timestamp > e.timestamp)

or

SELECT e.id, e.userId, e.timestamp, e.value
FROM example e
JOIN (
    SELECT userId, MAX(timestamp) AS timestamp
    FROM example 
    GROUP BY userId
) x on 
     e.userId = x.userId and e.timestamp = x.timestamp

Answer 2

SELECT  e.*
FROM    (
        SELECT  DISTINCT userid
        FROM    example
        ) eo
JOIN    example e
ON      e.id =
        (
        SELECT  id
        FROM    example ei
        WHERE   ei.userid = eo.userid
        ORDER BY
                userid DESC, `timestamp` DESC, id DESC
        LIMIT 1
        )

This will work correctly even if you have duplicates on timestamp.

Create an index on (userid, timestamp, id) for this to work fast.