Group By query to return one row per group of latest date? 1

[nstouffer]

I am working on a group by query:

Original Table:

-pk- -pk-
AssetID TsfrDate TsfrFrom TsfrTo
1522 09/01/2002 <null> TX
1522 10/01/2002 TX NV
1523 05/01/2002 MN IL
1523 06/01/2002 IL MI
1523 04/01/2002 <null> MN

Original table records the transfer activity of assets.
The current location of asset 1522 is NV and 1523 is MI.

The qry result I need is:

AssetID Current Loc
1522 10/01/2002 TX NV
1523 06/01/2002 IL MI

Just the current location of each asset based on the
latest transfer date.

Here's what I attempted:

A sorting select query:

QrySortAssets

AssetID TsfrDate From To
ASC DESC


Then a &quot;Group By&quot; qry based on QrySortAssets using the
function &quot;Last of&quot; or &quot;First of&quot; to grab the first row
of each assetid grouped on from the sorted query.

However, it seems to pay no attention to the sorting of the second query, and is still looking at the original tables order....

Any ideas? thanks!

nstouffer

 

[swampBoogie]

select * from assests as a
where tsfrDate in (select max(tsfrdate) from asssets
where assetid = a.assetid)

 

[nstouffer]

That worked Perfect! thank-you!

nstouffer