0

I have a table with three columns and several million rows of data.

sensor_data

sensor_id int PK
date datetime PK,
data decimal (10,3)

This query returns the most recent date for sensor 4, but the oldest data for sensor 4:

select max(date), data
from sensor_data
where sensor_id = 4;

These queries return correct values for data:

select date, data
from sensor_data
where sensor_id = 4
and date = (select max(date) from sensor_data where sensor_id = 4)
order by date desc;

select date, data
from sensor_data
where sensor_id = 4
order by date desc
limit 1;

I'm new to MySQL. Am I using max() wrong, or is this how it's supposed to work?

Dave
  • 4,546
  • 2
  • 38
  • 59
  • Allowing that query is a MySQLism, it's not really a valid SQL query. What it means is "get the max date and _any_ data from a row that matches the condition." Most if not all other RDBMS's reject the query entirely since you either have to use an aggregate function (a'la max/min) or a GROUP BY on any field that doesn't have an aggregate on it. – Joachim Isaksson Feb 09 '16 at 18:18
  • @JoachimIsaksson: it's been a while, but I could swear this works in SQL Server. – Dave Feb 09 '16 at 18:20
  • SQL Server fails with "Column 'sensor_data.data' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." – Joachim Isaksson Feb 09 '16 at 18:23
  • @JoachimIsaksson: Ah, that error message rings a bell. Thanks. – Dave Feb 09 '16 at 18:24
  • 1
    @JoachimIsaksson sybase also has this relaxed feature, teradata as well under limited circumstances. – Shadow Feb 09 '16 at 18:24
  • This is something of a FAQ – Strawberry Feb 09 '16 at 18:28

2 Answers2

1

This is due to mixing aggregate functions with non-aggregate columns. In PostgreSQL this would be an error, but in MySQL you get an arbitrary entry out of the rows used for the non-aggregate column value.

Jake Cobb
  • 1,811
  • 14
  • 27
  • 2
    In mysql this behaviour is configurable via the sql mode setting of the server. Since mysql 5.7.5 the relaxed group by syntax is disabled by default, before that it was allowed by default. As the older installations get upgraded, we will hopefully see less of these questions. – Shadow Feb 09 '16 at 18:29
  • @Shadow I had no idea bout it being "fixed" in newer MySQL. I can only say "hooray!" :) – Joachim Isaksson Feb 09 '16 at 18:43
  • @JoachimIsaksson you are not the only one, I would say :D – Shadow Feb 09 '16 at 18:45
0

You can just order your result by descending date:

SELECT data FROM sensor_data WHERE sensor_id = 4 ORDER BY date DESC LIMIT 1;

As mentioned elsewhere, MAX() is an aggregate function, meant to be used with GROUP BY.

miken32
  • 42,008
  • 16
  • 111
  • 154