Subquery returns more than 1 row - How to get the last update date of a row?

Question

I have a table with device ID and the date and time of the last update, I'm trying to make a MySQL query to get the last seen update of each device on the table, but when I execute my query, I'm getting the following error:

Subquery returns more than 1 row.

Below is the query that I'm using on my PHP code.

SELECT log_device_dt_lastupdate FROM tb_log_device 
WHERE device_id=(SELECT DISTINCT(device_id) 
FROM tb_log_device) ORDER BY log_device_dt_lastupdate 
DESC LIMIT 1

If I use GROUP_CONCAT I just get the information from one device.

I don't want to use two queries to get this info from the table, is it possible to have it? Maybe by making a JOIN?

Thanks.

score 1 · Accepted Answer · answered May 14 '19 at 04:20

1

use group by and max() aggregation

SELECT deviceid,max(log_device_dt_lastupdate) 
FROM tb_log_device 
group by deviceid

answered May 14 '19 at 04:20

Fahmi

37,315
5
22
31

Works perfect, but I can't get the row log_device_dt_lastupdate, only the device_id show in the php array, I tried SELECT deviceid, log_device_dt_lastupdate, max(log_device_dt_lastupdate) but not work too. I need the last update time to compare with actual timedate. – Paul Mark May 14 '19 at 15:41

score 0 · Answer 2 · answered May 14 '19 at 04:31

0

Use where_in

SELECT log_device_dt_lastupdate FROM tb_log_device 
WHERE device_id IN 
(SELECT device_id 
FROM tb_log_device 
ORDER BY log_device_dt_lastupdate DESC)

answered May 14 '19 at 04:31

PHP Geek

3,949
1
16
32

Thank you, but the query are returning a lot of old timestamps. – Paul Mark May 14 '19 at 19:29

Subquery returns more than 1 row - How to get the last update date of a row?

2 Answers2