mysql query to get latest records for each category

Question

I have 2 tables devices and locations. I need result like latest 5 records by deviceid where admin id. as json format tables as below: devices table:

    +-----------------------+
    | id |device_id|adminid |
    +-----------------------+
    |  1 |eefdcdfs | admin1 |
    |  2 |ffsdxwe2 | admin1 |
    |  3 |aaqw35ds | admin2 |
    |  4 |grfdr23f | admin2 |
    |  5 |etdhy79e | admin3 |
    +-----------------------+

locations table:

    +-----------------------------------------------------------------+
    | lid|lat       |long      |time               |deviceid |adminid |
    +----+----------+----------+-------------------+---------+--------+
    |  1 |17.4425358|78.3922061|2016-06-08 12:23:24|eefdcdfs | admin1 |
    |  2 |17.4425358|78.3922061|2016-06-08 12:45:24|eefdcdfs | admin1 |
    |  3 |17.4425358|78.3922061|2016-06-08 11:56:24|eefdcdfs | admin1 |
    |  4 |17.4425358|78.3922061|2016-06-08 12:53:24|eefdcdfs | admin1 |
    |  5 |17.4425500|78.3922342|2016-06-08 12:53:34|ffsdxwe2 | admin1 |
    |  6 |17.4425342|78.3922546|2016-06-08 11:55:34|ffsdxwe2 | admin1 |
    |  7 |17.4425562|78.3922657|2016-06-08 12:23:34|ffsdxwe2 | admin1 |
    |  8 |17.4425223|78.3922675|2016-06-08 12:12:34|ffsdxwe2 | admin1 |
    |  9 |17.4424856|78.3922307|2016-06-08 12:56:48|aaqw35ds | admin2 |
    | 10 |17.4425453|78.3922087|2016-06-08 13:08:30|grfdr23f | admin2 |
    | 11 |17.4425472|78.3922294|2016-06-08 13:15:54|etdhy79e | admin3 |
    +----+----------+----------+-------------------+---------+--------+

expected result:

    +-----------------------------------------------------------------+
    | lid|lat       |long      |time               |deviceid |adminid |
    +----+----------+----------+-------------------+---------+--------+
    |  4 |17.4425358|78.3922061|2016-06-08 12:53:24|eefdcdfs | admin1 |
    |  2 |17.4425358|78.3922061|2016-06-08 12:45:24|eefdcdfs | admin1 |
    |  1 |17.4425358|78.3922061|2016-06-08 12:23:24|eefdcdfs | admin1 |
    |  3 |17.4425358|78.3922061|2016-06-08 11:56:24|eefdcdfs | admin1 |
    |  5 |17.4425500|78.3922342|2016-06-08 12:53:34|ffsdxwe2 | admin1 |
    |  7 |17.4425562|78.3922657|2016-06-08 12:23:34|ffsdxwe2 | admin1 |
    |  8 |17.4425223|78.3922675|2016-06-08 12:12:34|ffsdxwe2 | admin1 |
    |  6 |17.4425342|78.3922546|2016-06-08 11:55:34|ffsdxwe2 | admin1 |
    +----+----------+----------+-------------------+---------+--------+

I tried like:

    select deviceid,CONCAT('[',CAST(lat AS CHAR ),',',CAST(long AS
    CHAR ),'],') json from locations WHERE 
    admin_id='admin1' 
    AND `time` > DATE_SUB(NOW(), 
    INTERVAL 3 HOUR) ORDER BY time DESC limit 10;

Why your expected result does not have `deviceid` 'aaqw35ds', 'grfdr23f', 'etdhy79e'? — Blank, Jun 08 '16 at 08:51

score 0 · Answer 1 · answered Jun 08 '16 at 08:52

This is a mysql query used in my program.Use jointo concat two table Distinct will avoid replication.

qry=Select distinct * from  tbl_1 join tbl_2 on tbl_1 .Som_Id =tbl_2 .S_Id  order by DATE1";

This is an example only for reference. By making some changes and you can achieve your result.

Kickstart · Answer 2 · 2016-06-08T09:38:09.677

One way to do this is using variables. Do a sub query to get the possible rows you are interested in, ordered by the time descending. Add 1 to the counter for each one where the device id is the same. The use those results as a sub query and return those where the counter is 5 or less.

Something like this:-

SELECT lid,
    lat,
    `long`,
    `time`,
    deviceid,
    adminid
FROM
(
    SELECT lid,
        lat,
        `long`,
        `time`,
        deviceid,
        adminid,
        @cnt:=IF(@deviceid = deviceid, @cnt + 1, 1) AS cnt,
        @deviceid := deviceid
    FROM
    (
        SELECT locations.lid,
            locations.lat,
            locations.`long`,
            locations.`time`,
            locations.deviceid,
            locations.adminid
        FROM devices
        INNER JOIN locations
        ON devices.device_id = locations.deviceid
        WHERE locations.adminid = 'admin1' 
        AND locations.`time` > DATE_SUB(NOW(), INTERVAL 3 HOUR)
        ORDER BY  locations.deviceid, locations.time DESC
    ) sub0
    CROSS JOIN 
    (
        SELECT @cnt:=0, @deviceid:=''
    ) sub1
) sub2
WHERE cnt <= 5

EDIT

An alternative is to use and abuse the GROUP_CONCAT statement to get all the times for each device, then SUBSTRING_INDEX to get the 5th time (if less than 5 times it will get the last one which is fine in this case), and then join that against your tables to get the records with the appropriate times:-

SELECT locations.lid,
        locations.lat,
        locations.`long`,
        locations.`time`,
        locations.deviceid,
        locations.adminid
FROM devices
INNER JOIN locations
ON devices.device_id = locations.deviceid
INNER JOIN      
(
    SELECT locations.deviceid,
        GROUP_CONCAT(locations.`time` ORDER BY locations.time DESC) AS times
    FROM devices
    INNER JOIN locations
    ON devices.device_id = locations.deviceid
    WHERE locations.adminid = 'admin1' 
    AND locations.`time` > DATE_SUB(NOW(), INTERVAL 3 HOUR)
    GROUP BY locations.deviceid
) sub0
ON locations.deviceid = sub0.deviceid
AND locations.`time` >= SUBSTRING_INDEX(SUBSTRING_INDEX(sub0.times, ',', 6), ',', -1)
WHERE locations.adminid = 'admin1'

@raavi don't forget to mark answers with a Green Check mark. Thx. The [tour](http://stackoverflow.com/tour) ... Kickstart had to work hard to do this, ya know. You have 8 or so questions, none are marked answered. — Drew, Jun 08 '16 at 22:54

mysql query to get latest records for each category

2 Answers2