Lit Aiy Lit Aiy - 4 months ago 7
SQL Question

Retrieve only last record of each entity and not all the records

I wrote this MySQL query below.

SELECT doors.mac_addr,
readers.reader_name,
reader_records.value,
reader_records.time_change
FROM (building.readers readers
INNER JOIN building.doors doors
ON (readers.gateway_id = doors.id))
INNER JOIN building.reader_records reader_records
ON (reader_records.reader_id = readers.id)
WHERE (doors.mac_addr = 'B99A88')
ORDER BY reader_records.time_change DESC


It produces the following result;

(mac_addr, reader_name, value, time_change) VALUES
('B99A88', 'name_8', 1, '7/7/2016 7:21:48 PM'),
('B99A88', 'own__detect_1', 1, '6/21/2016 1:30:00 PM'),
('B99A88', 'own__temperature_1', 37.4, '5/4/2016 6:23:03 PM'),
('B99A88', 'own__temperature_1', 29.4, '5/4/2016 6:19:33 PM'),
('B99A88', 'own__temperature_1', 28.4, '5/4/2016 6:17:32 PM'),
('B99A88', 'own__temperature_1', 27.4, '5/4/2016 6:04:08 PM'),
('B99A88', 'own__temperature_1', 21.4, '5/4/2016 3:11:42 PM'),
('B99A88', 'own__detect_1', 0, '4/20/2016 3:22:23 PM'),
('B99A88', 'own__detect_1', 1, '4/15/2016 5:39:52 PM'),
('B99A88', 'own__detect_1', 0, '4/15/2016 5:39:46 PM'),
('B99A88', 'own__detect_1', 1, '4/11/2016 5:34:00 PM'),
('B99A88', 'own__detect_1', 1, '4/11/2016 5:33:00 PM'),
('B99A88', 'own__detect_1', 0, '4/11/2016 5:33:00 PM'),
('B99A88', 'own__temperature_1', 28.4, '4/10/2016 9:20:20 PM'),
('B99A88', 'own__temperature_1', 32.5, '4/10/2016 9:00:00 PM'),
('B99A88', 'own__temperature_1', 34.2, '4/10/2016 11:29:00 AM')


However, it is not exactly what I want as it retrieves all records of each
reader_name
. What I want is to retrieve only the last record of each
reader_name
. The desired query I want should produce this output;

(mac_addr, reader_name, value, time_change)
('B99A88', 'name_8', 1, '7/7/2016 7:21:48 PM'),
('B99A88', 'own__detect_1', 1, '6/21/2016 1:30:00 PM'),
('B99A88', 'own__temperature_1', 37.4, '5/4/2016 6:23:03 PM'),


How should my query be modified to get the desired result?

EDIT: What if last X records of each entity is required? Say, last 2 records. The desired result if last 2 records are required looks like this;

(mac_addr, reader_name, value, time_change) VALUES
('B99A88', 'name_8', 1, '7/7/2016 7:21:48 PM'),
('B99A88', 'own__detect_1', 1, '6/21/2016 1:30:00 PM'),
('B99A88', 'own__detect_1', 0, '4/20/2016 3:22:23 PM'),
('B99A88', 'own__temperature_1', 37.4, '5/4/2016 6:23:03 PM'),
('B99A88', 'own__temperature_1', 29.4, '5/4/2016 6:19:33 PM')

Answer

Try this:

SELECT t1.*,
       IF(@rn = reader_name, @rowno := @rowno + 1, @rowno := 1) AS rowno,
       @rn := reader_name
FROM (
    SELECT doors.mac_addr,
           readers.reader_name,
           reader_records.value,
           reader_records.time_change
      FROM (building.readers readers
            INNER JOIN building.doors doors
               ON (readers.gateway_id = doors.id))
           INNER JOIN building.reader_records reader_records
              ON (reader_records.reader_id = readers.id)
     WHERE (doors.mac_addr = 'B99A88')
    ORDER BY reader_records.time_change DESC
) t1
CROSS JOIN (SELECT @rn := null, @rowno := 0) t2
HAVING rowno = 1
-- HAVING rowno <= 2

Edited:

SELECT mac_addr, reader_name, value, time_change
FROM (
    SELECT t1.*,
           IF(@rn = reader_name, @rowno := @rowno + 1, @rowno := 1) AS rowno,
           @rn := reader_name
    FROM (
        SELECT doors.mac_addr,
               readers.reader_name,
               reader_records.value,
               reader_records.time_change
          FROM (building.readers readers
                INNER JOIN building.doors doors
                   ON (readers.gateway_id = doors.id))
               INNER JOIN building.reader_records reader_records
                  ON (reader_records.reader_id = readers.id)
         WHERE (doors.mac_addr = 'B99A88')
        ORDER BY reader_records.time_change DESC
    ) t1
    CROSS JOIN (SELECT @rn := null, @rowno := 0) t2
) t
WHERE rowno <= 2
Comments