Help with getting newest record

[jln]

Im trying to creat a query that has only grabs the highest trans_code and the
highest Next pay date. Right now when i run the query im getting records that
show all the trans_codes for the Next pay date that is with in my date range.
I have tried doing last but im still getting more then one record when i only
need the newest record. Hope this is enought detail.

 

[Ken Snell \(MVP\)]

Perhaps this will work:

SELECT TOP 1 TableName.*
FROM TableName
WHERE TableName.[trans_code] =
(SELECT Max(T.[trans_code])
FROM TableName AS T
WHERE T.[Next] =
(SELECT Max(TT.[Next]))
FROM TableName AS TT))
ORDER BY TableName.[Next] DESC;