(my)SQL: Select 20 latest entries in logfile from unique persons

Question

hi,

I have a logfile which logs the insert/delete/updates from all kinds of tables. I would like to get an overview of for example the last 20 people which records where updated, ordered by the last update datetime DESC

What I have now is:

SELECT DISTINCT logfile.idvalue, DATE_FORMAT(logfile.logDateTime,'%d-%m-%Y  %H:%i') AS thedatetime, CONCAT_WS(' ',people.peopleSurname,people.peopleLastname) AS peopleName
FROM logfile,people
WHERE 0=0
AND logfile.tablename='people'
AND logfile.action='update'
AND logfile.idvalue=people.peopleID
GROUP BY logfile.idvalue
ORDER BY logfile.logDateTime DESC,logfile.idvalue DESC

But this results in logDateTimes which don't correspond to the datetime of the latest (Max) "update-entry" What am i missing?!

thanks Bart

Answer 1

SELECT  *
FROM    logfile lf
WHERE   tablename = 'people'
        AND action = 'update'
        AND logdatetime = 
        (
        SELECT  MAX(logdatetime)
        FROM    logfile lfi
        WHERE   lfi.idvalue = lf.idvalue
                AND lfi.tablename = lf.tablename
                AND lfi.action = lf.action
        )
ORDER BY
        logdatetime DESC
LIMIT 20

Create the following indexes:

(tablename, action, idvalue, logdatetime)
(tablename, action, logdatetime)

for this to work fast.

Answer 2

Perhaps this might be some use...

SELECT *
    FROM (SELECT MAX(logdatetime) AS latest_time,
                 idvalue          AS idvalue
              FROM logfile
              WHERE tablename = 'people' AND
                    action    = 'update'
              GROUP BY idvalue) AS max_times
    ORDER BY latest_time DESC
    LIMIT 20;

Answer 3

Problem

If you are selecting the logDateTime within the same query, you are going to get result for every datetime the person has updated the table, i.e. multiple results per person.

Solution

Instead use MAX to select the latest datetime for that user. You can order by the MAX value also using the alias.

Tested and working code (no sub-query!)

SELECT DISTINCT 
l.idvalue, 
CONCAT_WS(' ',p.peopleSurname,p.peopleLastname) AS peopleName,
DATE_FORMAT(MAX(l.logDateTime),'%d-%m-%Y  %H:%i') as thedatetime
FROM 
logfile l INNER JOIN people p ON l.idvalue = p.peopleID
WHERE
l.tablename='people' AND l.action='update'
GROUP BY 
l.idvalue, p.peopleSurname, p.peopleLastname
ORDER BY 
thedatetime DESC, l.idvalue DESC
LIMIT 0, 20;

I've used table a alias for people and logfile to make it a bit easier to read and switched to more common join syntax.