March 9, 2008 at 4:03 pm
I have two tables:
Table A Table B
ItemNumber ItemID
ItemName StatusDate
StatusDetail
data in the tables:
Table A Table B
1 1
abc 2/3/2008
detail abc
1
3/3/2008
detail def
1
3/5/2008
detail ghi
Table A and B are related one to many .
What I want to get is:
ItemNumber ItemName StatusDetail StatusDate
1 abc detail ghi 3/5/2008
i.e. I want the latest status detail.
Please can anyone help?
Thanks
🙂
March 9, 2008 at 5:01 pm
As the most up-to-date status for an item is likely to be important I would enclose this functionality in a view.
CREATE VIEW LastItemDetail
AS
SELECT ItemID,StatusDate,StatusDetail
FROM TableB AS B
INNER JOIN (
SELECT ItemID, MAX(StatusDate) AS LastStatusDate
FROM TableB
) AS M
ON B.ItemID = M.ItemID
AND B.StatusDate = M.LastStatusDate
You can then do a straight join between Table A and your view.
March 9, 2008 at 5:13 pm
Thanks!
April 8, 2008 at 10:56 am
Great answer! This has helped me no end as well. Is ther another slution not involving a view - I have need of this without the use of iews.:)
April 8, 2008 at 12:21 pm
This does it without using a view:
SELECT ITS.ItemNumber, ITS.ItemName,ITS.StatusDetail,ITS.StatusDate
FROM MAIR_tblItemStatus ITS INNER JOIN
(SELECT MAX(ITS.StatusDate) AS MaxDate, ITS.ItemID
FROM MAIR_tblItemStatus ITS
GROUP BY ITS.ItemID) Sub
ON ITS.ItemID = Sub.ItemID AND ITS.StatusDate = Sub.MaxDate
Hope this helps.
🙂
April 8, 2008 at 12:31 pm
Brilliant! What can I say, apart from thank you for the help - Outstanding!
April 9, 2008 at 3:39 am
Is this applicable to sql 2000?
April 9, 2008 at 7:06 am
Yes.
April 13, 2008 at 11:26 pm
Ty, now its working, but if I add a field in the Query from table B, I'm getting an error of the field is not part of an aggregate. How can I get the additional field that has the max(date) on its row? e.g Max(Itemdate), ItemID,ItemPurcCost
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply