Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

nstouffer

Programmer
Jan 11, 2002
52
US
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
 
select * from assests as a
where tsfrDate in (select max(tsfrdate) from asssets
where assetid = a.assetid)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top