在MySQL中选择多个唯一行

I've got a table with the following columns:

我有一个包含以下列的表：

ID, sysid, x, y, z, timereceived

ID，sysid，x，y，z，timereceived

ID is a unique number for each row.

ID是每行的唯一编号。

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

sysid是特定设备的ID号（大约100种不同的）

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

x，y和z是从设备接收的数据。（完全随机的数字）

timereceived is a timestamp for when the data was received.

timereceived是收到数据时的时间戳。

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

我需要一个SQL查询来向我显示设备a，设备b，设备c等的最后插入行。

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).

我一直在玩很多不同的Select语句，但从来没有得到任何有用的东西。我设法通过使用group by获得唯一的行，但其余信息是随机的（或者至少感觉非常随机）。

Anyone able to help me?

有人能帮帮我吗？

There could be hundreds of thousands records in this table.

此表中可能有数十万条记录。

3 个解决方案

#1

    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.

这将为您提供完整的行，其中timeedceived是每个sysid最新的行。

#2

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.

不确定速度，因为我没有一个大表来测试它。确保有关sysid和timereceived的索引，这将有所帮助。实际上，对每个设备进行查询可能会更快。

#3

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)

#1

    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)