SQL phpmyadmin query return most recently date

Question

i have a table in my phpmyadmin database with this colomn and data:

id    sensor    temp    hum    date

--------------------------------------

1     sensor1   15      20     20/12/2016
2     sensor1   18      0      2/10/2015
3     sensor2   22      10     20/12/2016
4     sensor2   30      22     2/10/2015
5     sensor3   25      15     20/12/2016

I want a query that return data like:

id    sensor    temp    hum    date

--------------------------------------

1     sensor1   15      20     20/12/2016
3     sensor2   22      10     20/12/2016
5     sensor3   25      15     20/12/2016

So i want for each sensor, the most recent date and information. How can i do this with a query? Thanks all for help.

EDIT

My date are store in this format:

Thu Sep 29 2016 11:42:59 GMT+0200 (CEST)

EDIT2

I have notated that on my db is activated the only_full_group_by

dev.mysql.com/doc/refman/5.7/en/…

Abhik Chakraborty
– Abhik Chakraborty

2016-10-04 14:41:48 +00:00
Commented Oct 4, 2016 at 14:41 — Abhik Chakraborty
– Abhik Chakraborty, Commented Oct 4, 2016 at 14:41

gherkin · Accepted Answer · 2016-10-04 14:48:39Z

1

Try

select `id`,`sensor`,`temp`,`hum`, max(`date`)
from table
GROUP BY  `sensor`;

edited Oct 4, 2016 at 14:48

answered Oct 4, 2016 at 14:42

gherkin

4967 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3836982 Over a year ago

I have try your query without 'id' because it isn't important for my aim, it return me an error like: #1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'WiFiNetwork.WiFiTH.temp' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

gherkin Over a year ago

try add temp and hum to group by clause as well

user3836982 Over a year ago

it dont return any error, but it return me all the table

Collectives™ on Stack Overflow

SQL phpmyadmin query return most recently date

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related