October 28, 2005 at 1:34 am
I am having a problem trying to create a query that retrieves data from a table in the following scenario
Product No Location Date
1234 ABC 2005-10-05
4567 ABC 2004-09-10
7890 ABC 2005-04-14
4567 RTY 2005-11-11
In the table above I want to retrieve all the Product No and Locations record values from all the records in the table and when there is a duplicate Product No record and want to retrieve the Product No and Location record that has the latest Date.
Hence query result would look like this
Product No Location
1234 ABC
7890 ABC
4567 RTY
Cheers Charles
October 28, 2005 at 1:40 am
select product_no,
location = (select top 1 location from table1 x
where x.product_no = t.product_no
order by date desc)
from table1 t
group by product_no
October 28, 2005 at 2:12 am
That works great, thanks for the quick reply !!
October 28, 2005 at 3:56 am
The previously provided solution will not work as it will return multiple rows when there is more than one row for the same productNo and location.
ProductNo Location Date
1234 ABC 2005-10-05
1234 RTY 2005-10-04
1234 ABC 2005-10-03
Try instead:
select MyTable.ProductNo
, MyTable.Location
, MyTable.Date
from MyTable
join
(select ProductNo
, MAX(Date) Latest_Date
from MyTable
group by ProductNo ) as MyTable_Latest
on MyTable.ProductNo = MyTable_Latest.ProductNo
and MyTable.Date = MyTable_Latest.Latest_Date
SQL = Scarcely Qualifies as a Language
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply