I need some help please, I have this sql statement to display the latest records in each device_id,
but how do I display 5 records in each device_id ?
example 5 records for the 1010,5 records for 1020 and 5 records for 1030
putting LIMIT after where clause is not working.how do I change the sql satement. ?
SELECT dv.device_id AS 'devid',dv.dateTime_created AS 'thetime',userdev.device_name AS 'devname',dv.id
FROM user_devices userdev
INNER JOIN
(
SELECT device_id, MAX(id) AS 'latest'
FROM device_locations
GROUP BY device_id
) AS d ON d.device_id = userdev.device_id
INNER JOIN
device_locations dv ON dv.device_id = d.device_id AND dv.id = d.latest
WHERE dv.device_id IN (1010,1020,1030) AND userdev.user_id='1'
Here is the results I want,…I apologize for not supplying the date in the thetime column,but I want to display the date in descening order.
devid thetime devname
1010 sectorA
1010 sectorA
1010 sectorA
1010 sectorA
1010 sectorA
1020 sectorB
1020 sectorB
1020 sectorB
1020 sectorB
1020 sectorB
1030 sectorC
1030 sectorC
1030 sectorC
1030 sectorC
1030 sectorC
here is my demo
Thank you in advance.