Geek Answers Handbook

Get only the last record of each object, not all 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, this is not exactly what I want, as it retrieves all the entries of each reader_name . I want to get only the last entry of each reader_name . The desired query I want should produce this conclusion;

 (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 request be modified to get the desired result?

EDIT: What if the last X records of each object are required? Say the last 2 entries. The desired result, if the last 2 entries are required, is as follows:

 (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')
+5
source share
5 answers

Try the following:

 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 readers.reader_name, reader_records.time_change DESC ) t1 CROSS JOIN (SELECT @rn := null, @rowno := 0) t2 HAVING rowno = 1 -- HAVING rowno <= 2

Edited by:

 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 readers.reader_name, reader_records.time_change DESC ) t1 CROSS JOIN (SELECT @rn := null, @rowno := 0) t2 ) t WHERE rowno <= 2
+11
source

A simple solution if the last entry to the group is enough:

 SELECT d.mac_addr, r.reader_name, SUBSTRING_INDEX(GROUP_CONCAT(rr.value ORDER BY rr.time_change DESC), ',', 1) AS value SUBSTRING_INDEX(GROUP_CONCAT(rr.time_change ORDER BY rr.time_change DESC), ',', 1) AS time_change FROM (building.readers r INNER JOIN building.doors d ON (r.gateway_id = d.id)) INNER JOIN building.reader_records rr ON (rr.reader_id = r.id) WHERE (d.mac_addr = 'B99A88') GROUP BY d.mac_addr, r.reader_name ORDER BY d.mac_addr, r.reader_name;

With some customization, N last lines can also be achieved, although the solution will be far away. See this blog post .

+3
source

STOP PRESS : MySQL does not support window processing functions! So this will not work for you, sorry.

Try using a window function, for example ROW_NUMBER :
SELECT mac_addr, reader_name, VALUE, time_change FROM (SELECT doors.mac_addr, readers.reader_name, reader_records.VALUE, reader_records.time_change, ROW_NUMBER() OVER ( PARTITION BY doors.mac_addr, readers.reader_name ORDER BY reader_records.time_change DESC ) rowno 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')) WHERE rowno = 1 ORDER BY reader_records.time_change DESC
Window functions are part of the ANSI standard, so they usually work the same in databases, my example was from Oracle. When you have the basics, there are other useful window features, such as LEAD and LAG , which you can use to indicate how long the door has been left open.

+3
source

If ur uses SQL USE TOP 1 to get TOP 1 row

 SELECT TOP 1 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

In Mysql Use LIMIT

 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 LIMIT 1
+2
source

One solution is to use a subquery that returns the maximum (last) time_change for each record_id:

 select id, max(time_change) as latest_time_change from reader_records group by id

you can insert the previous request into the original request:

 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 ( select id, max(time_change) as latest_time_change from reader_records group by id ) mr ON reader_records.reader_id=mr.reader_id INNER JOIN building.reader_records reader_records ON mr.reader_id = readers.id AND mr.latest_time_change=readers.time_change where doors.mac_addr = 'B99A88' order by reader_records.time_change DESC
+2
source

More articles:


All Articles