March 4, 2002 at 4:09 pm
Hi,
I have a table StatusLog (LogID, Status, StatusDate) which store history of statuses for each LogID. For example, LogID = 1 will have 2 statuses "Requested" and "Send" (2 records), LogID = 2 can have 3, "Requested", "Send" and "Shipped" (3 records). Ofcourse StatusDate stores corresponded date. I need to find the way to extract current (most recent Status) for each Log. Using MAX(StatusDate) does not produce result if you have Status column in resulting query. Any idea? Thanks
March 4, 2002 at 4:21 pm
Can you post what you tried alogn with some more sample data. Not entirely sure what you mean. Also include the table DDL.
Steve Jones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 4, 2002 at 5:13 pm
Also, how about adding an auto-number identity field for each new insert, and pull your data by the criteria as normal, then max(ID), giving you the latest record? Or are there updates-only involved? Maybe a second date/time field with a timestamp for each update, giving you original date/time AND updated date/time that you can do the Max function on?
March 4, 2002 at 5:21 pm
Ok guys does this not sound simply like this.
SELECT StatusLog.LogID, StatusLog.Status, StatusLog.StatusDate FROM StatusLog
INNER JOIN
(SELECT LogID, Max(StatusDate) as MaxStatusDate FROM StatusLog GROUP BY LogID) AS MaxTbl
ON
StatusLog.LogID = MaxTbl.LogID AND
StatusLog.StatusDate = MaxTbl.MaxStatusDate
You are just trying to find the Status for each log on the latest date. This should do it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply