Problem with MAX() function

Chunk

Registered User.
Local time
Today, 09:44
Joined
Oct 25, 2004
Messages
64
Code:
SELECT Max(date_of_status_change) as [date_change], end_status
FROM Order_status_history
WHERE order_number = 39;

I cant get the above query to work. All I want it to do is pick out all the rows where the order_number is 39. Then select the one which has the most recent date_of_status_change, and show the "end_status" field.

I cant understand what is wrong with the query.
 
SELECT Max([date_of_status_change]) as [date_change], [end_status]
FROM [Order_status_history]
WHERE [order_number] = 39;

Try that!
 
Isn't the problem that end-status is not part of an aggregate expression?
I think it will require 2 queries one to find the max date, remove the select of end-status from the current query, then join this max date query field to the table date and select your fields again incuding the where.

Brian
 
Brianwarnock said:
Isn't the problem that end-status is not part of an aggregate expression?
I think it will require 2 queries one to find the max date, remove the select of end-status from the current query, then join this max date query field to the table date and select your fields again incuding the where.

Brian

SJ: That didnt work. Brian is right about the problem. However I dont understand why end_status needs to be a part of the aggregrate. I will try what you suggested Brian.

EDIT: Do you mean like this:

Code:
SELECT end_status
FROM Order_status_history
WHERE [order_number] = 39 AND EXISTS
      (SELECT Max([date_of_status_change]) as [latest_date_of_status]
      FROM [Order_status_history]
      WHERE latest_date_of_status = [date_of_status_change])
;

Or like this:

Code:
SELECT end_status
FROM Order_status_history  INNER JOIN
(SELECT Max([date_of_status_change]) as [latest_date_of_status]
FROM [Order_status_history]
WHERE latest_date_of_status = [date_of_status_change]) ON (date_of_status_change);

Neither way will work for me.
 
Last edited:
However I dont understand why end_status needs to be a part of the aggregrate.
In a query that aggregates data, there is no way to identify the individual source row(s) of any data. That means that you need to either specify an aggregation function for a column to indicate which value you want returned or group by it if you want to have grouped output. There is no way to tie the status value to the record of the Max(date). You could use First(), Last(), Min(), Max() but they all work independently. So the same query can return the Min(date) and the Max(date) as well as the First(date) and the Last(date) and they can all be different and come from different records.
 
Your queries will be as below give or take a typo or 2 :D

firstquery
Code:
SELECT Max(date_of_status_change) as [date_change]
FROM Order_status_history
WHERE order_number = 39;

Secondquery

Code:
SELECT Order_status_history.end_status, firstquery.date_change
FROM Order_status_history INNER JOIN firstquery ON Order_status_history.date_of_status_change = firstquery.date_change
WHERE Order_status_history.order_number=39;


Brian
 

Users who are viewing this thread

Back
Top Bottom