Max Query Issue
I am trying to perform the following query to get the max value between a certian time range:
Select id,reference,max(value) from data_readings.lts4a_accel_torquer where reference='total_parts' and readtime between '2005-5-20 6:30:0' and '2005-5-20 12:41:46' group by reference;
the result is
6570,total_parts,996
if I go to the data table the entry at id 6570 is:
6570,total_parts,0
I search the database for any entry with a value=996 and got this:
3300,total_parts,996
6850,total_parts,996
if use this query
Select id,reference,max(value) from data_readings.lts4a_accel_torquer where reference='total_parts' group by reference;
i get a result of:
5,total_parts,996
If I manually search the table the highest total_parts value is:
2480,total_parts,2326
The table setup is as follows:
id autoincrement not null int,reference varchar(50),value varchar(20),readtime timestamp,trigger_type varchar(20)
If I preform the previous queries on the id column, it works fine
here is a sample of the table
2497, 'failed_parts', '0', '2005-05-17 06:47:17', 'interval'
2498, 'parts_ratio', '100', '2005-05-17 06:47:17', 'interval'
2499, 'passed_parts', '58', '2005-05-17 06:47:17', 'interval'
2500, 'total_parts', '58', '2005-05-17 06:47:17', 'interval'
2501, 'accel_nut_failure', '0', '2005-05-17 06:52:17', 'interval'
2502, 'failed_parts', '0', '2005-05-17 06:52:17', 'interval'
2503, 'parts_ratio', '100', '2005-05-17 06:52:17', 'interval'
2504, 'passed_parts', '82', '2005-05-17 06:52:17', 'interval'
2505, 'total_parts', '82', '2005-05-17 06:52:17', 'interval'
2506, 'accel_nut_failure', '0', '2005-05-17 06:57:17', 'interval'
2507, 'failed_parts', '0', '2005-05-17 06:57:17', 'interval'
2508, 'parts_ratio', '100', '2005-05-17 06:57:17', 'interval'
2509, 'passed_parts', '97', '2005-05-17 06:57:17', 'interval'
2510, 'total_parts', '97', '2005-05-17 06:57:17', 'interval'
2511, 'accel_nut_failure', '0', '2005-05-17 07:02:17', 'interval'
2512, 'failed_parts', '0', '2005-05-17 07:02:17', 'interval'
2513, 'parts_ratio', '100', '2005-05-17 07:02:17', 'interval'
2514, 'passed_parts', '97', '2005-05-17 07:02:17', 'interval'
2515, 'total_parts', '97', '2005-05-17 07:02:17', 'interval'
2516, 'accel_nut_failure', '0', '2005-05-17 07:07:17', 'interval'
2517, 'failed_parts', '0', '2005-05-17 07:07:17', 'interval'
2518, 'parts_ratio', '100', '2005-05-17 07:07:17', 'interval'
2519, 'passed_parts', '97', '2005-05-17 07:07:17', 'interval'
what am I doing wrong?