Description:
I am having trouble with the max function and I am not sure it is me or the software. I have a table with an id, a value, a timestamp, and a sequence number in each row.
The following query returns
```
SELECT
deviceIdText,
value,
sequenceNumber,
timestamp
FROM
DevicesTelemetry.TMMeterCount where deviceId = x'0a17216927655931bd40b43971aae394' and day(timestamp) = 10;
```
RESULTS
```
deviceIdText value sequenceNumber timestamp
0A172169-2765-5931-BD40-B43971AAE394 99933 42975 2016-02-10 10:35:45
0A172169-2765-5931-BD40-B43971AAE394 99940 42980 2016-02-10 10:37:36
0A172169-2765-5931-BD40-B43971AAE394 99941 42987 2016-02-10 10:38:21
0A172169-2765-5931-BD40-B43971AAE394 99942 43005 2016-02-10 10:41:20
0A172169-2765-5931-BD40-B43971AAE394 99946 43006 2016-02-10 10:41:42
0A172169-2765-5931-BD40-B43971AAE394 99954 43019 2016-02-10 10:42:49
0A172169-2765-5931-BD40-B43971AAE394 99961 43028 2016-02-10 10:44:18
0A172169-2765-5931-BD40-B43971AAE394 99970 43033 2016-02-10 10:45:25
0A172169-2765-5931-BD40-B43971AAE394 99972 43035 2016-02-10 10:45:47
0A172169-2765-5931-BD40-B43971AAE394 99981 43044 2016-02-10 10:46:54
0A172169-2765-5931-BD40-B43971AAE394 99989 43054 2016-02-10 10:48:24
0A172169-2765-5931-BD40-B43971AAE394 99991 43062 2016-02-10 10:49:31
0A172169-2765-5931-BD40-B43971AAE394 99998 43065 2016-02-10 10:49:53
0A172169-2765-5931-BD40-B43971AAE394 99990 43083 2016-02-10 10:52:41
```
With this next query I am expecting to return the min and maximum value for the range returned above. As you can see the maximum value returned is the value from the second-last row of the available data. I have also used the sequence number which is returning the right value.
```
select deviceId, min(sequenceNumber), max(sequenceNumber), min(value), max(value)
from TMMeterCount
where deviceId = x'0a17216927655931bd40b43971aae394' and day(timestamp) = 10 ;
```
```
deviceId min(sequenceNumber) max(sequenceNumber) min(value) max(value)
0a17216927655931bd40b43971aae394 42975 43083 99933 99998
```
How to repeat:
Create a table
```
CREATE TABLE `TMMeterCount` (
`deviceId` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`value` bigint(20) DEFAULT NULL,
`sequenceNumber` bigint(20) NOT NULL,
`timestamp` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`deviceId`,`sequenceNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```
Insert Data
```
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99933,42975,'2016-02-10 10:35:45');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99940,42980,'2016-02-10 10:37:36');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99941,42987,'2016-02-10 10:38:21');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99942,43005,'2016-02-10 10:41:20');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99946,43006,'2016-02-10 10:41:42');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99954,43019,'2016-02-10 10:42:49');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99961,43028,'2016-02-10 10:44:18');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99970,43033,'2016-02-10 10:45:25');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99972,43035,'2016-02-10 10:45:47');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99981,43044,'2016-02-10 10:46:54');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99989,43054,'2016-02-10 10:48:24');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99991,43062,'2016-02-10 10:49:31');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99998,43065,'2016-02-10 10:49:53');
INSERT INTO TMMeterCount (deviceId,value,sequenceNumber,timestamp) values (x'0A172169-2765-5931-BD40-B43971AAE394',99990,43083,'2016-02-10 10:52:41');
```
Run the queries.
Suggested fix:
No suggestion.
The table that the data is coming from only has 12000 rows.