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...

Solution 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;

Solution 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')

Comments

[usumoio]

Good answer, this is obvious after you explained it, but I've been looking for this for about an hour.

[adamF]

This works, but is there a way to do it without sorting the entire result? Will the query optimizer find an efficient way to find the max?

[Andriy M]

@adamF: Well, I'm not sure you can determine if a value is greater than any other value in a set without going through the entire set. As for your second question, sorry, don't have much knowledge about the query optimiser (at least not about the MySQL one). But then I wouldn't worry about whether the query optimiser can come up with a good execution plan for my query. Express your request as simply as possible and let the optimiser find the best way to run it – that's what it is there for, after all. If/when there are issues with performance, then start thinking of ways to improve it.

[adamF]

Well sorting takes O(nlgn), but finding the max element only takes O(n). My guess is the optimizer recognizes the pattern order by desc limit k and finds the k max elements (provided k<<n) in sorted order. But you're right, I'll look into it if and when when it causes problems... just curious :)