MySQL using MAX () with WHERE clauses

Question

I am having trouble creating mySQL query with PHP. We have one table, called data_instant, with an introduced sequence of cumulative observations called Count_Qty, and we want to extract the previous one to subtract from the new observation to calculate the increase.

$result = mysql_query("SELECT *, MAX(Record_Time) FROM data_instant WHERE Node_ID='$nodeID' AND Type='$type'; $row = mysql_fetch_array ($result); 

Basically, I would expect the maximum Record_Time string to be returned, but instead it will be only the first instance received. The previous observation has the highest Record_Time (Unix installation date), so it’s not clear to me why this will not work ...

Answer 1

If you want to get a row with the last Record_Time value, just sort the rows in descending order Record_Time and get the top row:

 SELECT * FROM data_instant WHERE Node_ID='$nodeID' AND Type='$type' ORDER BY Record_Time DESC LIMIT 1; 

Answer 2

The where clause selects all rows matching Node_ID = '$ nodeID' AND Type = '$ type'.

For each of these lines, it will return all fields and the maximum recording time.

If you need a string with the maximum recording time, you need to add this to the where clause:

 SELECT * FROM data_instant WHERE Node_ID='$nodeID' AND Type='$type' and Record_Time = (select MAX(Record_Time) FROM data_instant WHERE Node_ID='$nodeID' AND Type='$type') 

Answer 3

If you have more than MAX returned, you must add GROUP BY to your statement to ensure proper aggregation:

 SELECT columnA, columnB, MAX(Record_Time) FROM data_instant WHERE Node_ID='$nodeID' AND Type='$type' GROUP BY columnA, columnB; 

Answer 4

I expect the maximum Record_Time string will return

Then you should just ask about this column:

 SELECT MAX(Record_Time) FROM data_instant WHERE Node_ID='$nodeID' AND Type='$type' 

This will return the maximum record_time for the specified node_id and enter.