Problem with MAX() function

[Chunk]

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.

 

[Mile-O]

SELECT Max([date_of_status_change]) as [date_change], [end_status]
FROM [Order_status_history]
WHERE [order_number] = 39;

Try that!

 

[Brianwarnock]

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

 

[Chunk]

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:

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:

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.

 

[Brianwarnock]

Your queries will be as below give or take a typo or 2

firstquery

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

Secondquery

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