MySQL using MAX() with WHERE clauses

Question

I am having some issues creating a mySQL query with PHP. We have one table, called data_instant, with a series of cumulative observations being entered, called Count_Qty, and we want to extract the previous one to deduct 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'd expect the max Record_Time row to be returned, but instead it's just the first instance that's received. The previous observation has the highest Record_Time (a Unix datestamp), so it's unclear to me why this would not work...

Answer 1

If you want to get the row with the latest Record_Time value, just sort the rows in the descending order of 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 those rows it will return all fields and the maximum record time.

If you want the row with the maximum record time you need to add that to your 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')