Help finding "most recent date" in query 1

[ReK410]

This is probably a really easy answer but I'm no TSQL guy so any help would be greatly appreciated!

TABLE1 holds:
po_no
item_id
received_date
(multiple entries for each item ID, each with a unique received_date)

TABLE2 holds:
po_no
location_id

MY GOAL IS: Show me each item at location 100 and it's most recent received date but every attempt to use MAX to restrict to the most recent date fails.

How should I be structuring this query?

 

[gmmastros]

Try this:

Select A.*
From   table2
       Inner Join (
         Select po_no, Item_ID, Max(Received_Date) As MaxDate
         From   Table1
         Group By po_no, Item_Id
         ) As A
         On table2.po_no = A.po_no
Where  table2.location_id = 100

If this works, and you would like me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

One thing everyone always forget to add is the SQL Server version.

Anyway, check the same question here

http://forums.asp.net/p/1423102/3163002.aspx#3163002

 

[bborissov]

SELECT Table1.Po_No,
       Table1.ItemId,
       MAX(Table1.received_date) AS received_date
FROM Table1
INNER JOIN Table2 ON Table1.Po_No = Table2.Po_No AND 
                     Table2.Location_Id = 100
GROUP BY Table1.Po_No,
         Table1.ItemId

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Though I see that you wanted to get all locations and show the latest date for all of them.

 

[ReK410]

Thank you for the help guys! I've got it working now!