Query to get the latest data of a group by data in a MySQL table

Question

I have a SQL table where I am trying to extract the latest data for each category.

My table schema

| deviceId                    | varchar(30) | NO   |     | NULL    |       |
| rssi                        | varchar(15) | YES  |     | NULL    |       |
| space_temperature           | varchar(30) | YES  |     | NULL    |       |
| control                     | varchar(30) | YES  |     | NULL    |       |
| counter                     | varchar(30) | YES  |     | NULL    |       |
| timestamp                   | varchar(30) | NO   |     | NULL    |       |
| date                        | varchar(30) | NO   |     | NULL    |       |
| floor                       | varchar(30) | YES  |     | NULL    |       |
| Id                          | varchar(5)  | YES  |     | NULL    |       |

I want to fetch the latest row for each Id.

select * from Data where floor = 'L2' and date = '2019-12-10' group by Id;

This is giving me a row for each Id, but it is giving me the first entry but not the latest entry in the table.

I tried with

select *,max(timestamp) as latest_timestamp from Data where floor = 'L2' and date = '2019-12-10' group by Id;

This is giving the latest timestamp but the other columns have the data of the first entry. How to get the results of the latest entry for each Id group?

Give some sample data with expected output. – DineshDB Dec 10 '19 at 05:20 — DineshDB, Dec 10 '19 at 05:20

score 1 · Answer 1 · answered Dec 10 '19 at 05:21

1

You can try below way -

select * from Data a where floor = 'L2' and date = '2019-12-10'
and timestamp =(select max(timestamp) from data b where a.id=b.id)

answered Dec 10 '19 at 05:21

Fahmi

37,315
5
22
31

I am trying to run this query, its taking forever to run. Will adding indexes help ? – chink Dec 10 '19 at 06:24

Query to get the latest data of a group by data in a MySQL table

1 Answers1