Select multiple unique lines in MySQL

Question

Hi,

I've got a table with the following columns:

ID, sysid, x, y, z, timereceived

ID is a unique number for each row.

sysid is an ID number for a specific device (about 100 different of these)

x, y and z is data received from the device. (totally random numbers)

timereceived is a timestamp for when the data was received.

I need a SQL query to show me the last inserted row for device a, device b, device c and so on.

I've been playing around with a lot of different Select statements, but never got anything that works. I manage to get unique rows by using group by, but the rest of the information is random (or at least it feels very random).

Anyone able to help me?

There could be hundreds of thousands records in this table.

Answer 1

SELECT sysid, MAX(timereceived) FROM yourtable GROUP BY sysid

Not sure on speed because I don't have a large table to test it on. Make sure there's an index on sysid and timereceived and that would help. It may actually be faster to do a query for each one of your devices.

Answer 2

SELECT ID, sysid, x, y, z, MAX(timereceived) as max_time FROM `table` GROUP BY sysid

UPDATED:

SELECT t1.ID, t1.sysid, t1.x, t1.y, t1.z, t1.timereceived 
    FROM `table` as t1
    JOIN (
      SELECT sysid, MAX(timereceived) as max_time
      FROM `table`
      GROUP BY sysid
    ) AS t2 ON (t2.sysid = t1.sysid AND t2.max_time = t1.timereceived)

Answer 3

    SELECT id, sysid, x, y, z, timereceived
      FROM tbl
INNER JOIN (  SELECT sysid, max(timereceived) as "timereceived"
                FROM tbl
            GROUP BY sysid ) most_recent
     USING (sysid, timereceived)

This will give you complete rows where the timereceived is the most recent per sysid.