Problem to display only the latest record on the query.

[awinnn]

Hi,
I have a problem with displaying only the latest record. This is what i've done,

SELECT VehicleNo, StaffNo, Max(ReceiveDate)
FROM tblMain
GROUP BY VehicleNo, StaffNo;

This is the sample of data,

VehicleNo StaffNo ReceiveDate
WFF9949 50424 08/05/2003
WFF9949 80690 12/09/2003

The latest record should be the 2nd record. But the problem is, both record are displayed on the query. This is because of the different StaffNo. Is there any other way to solve this problem? I need to include the StaffNo.
Any idea?
Thanx in advance..

 

[TheAceMan1]

How are ya awinnn . . . .

Try this:

[blue]SELECT [purple]Top 1[/purple] VehicleNo, StaffNo, Max(ReceiveDate)
FROM tblMain
GROUP BY VehicleNo, StaffNo
[purple]ORDER BY ReceiveDate DESC[/purple];[/blue]

See Ya! . . . . . .

 

[awinnn]

hI,
Thanx for reply....
The code is work if only all the VehicleNo in the record is same. What if i have more than 1 VehicleNo?
As for example,

VehicleNo StaffNo ReceiveDate
WFF9949 50424 08/05/2003
BFF9877 53200 06/01/2003
WFF9949 80690 12/09/2003
ACD5140 50424 16/05/2003

any idea? thanx..

 

[KenReay]

Hi

What if more than one record has the highest date? eg two of more records for different vehicles with same date and that date is the highest?

Anyway my guess would be

SELECT VehicleNo, StaffNo, ReceiveDate
FROM tblMain
WHERE ReceiveDate = (SELECT Max(Receivedate) FROM tblMain);

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[TheAceMan1]

awinnn . . .

Sorry . . . the [blue]Group By[/blue] line should be removed.

This will give you the latest date, but the amibguity mentioned by [blue]Roy[/blue] still remains. You need to make a decision on this!

See Ya! . . . . . .

 

[jksmi]

Hi,
adapting Ken's code slighty...

SELECT Last(VehicleNo) as LastVehicleNo, Last(StaffNo) as LastStaffNo, ReceiveDate
FROM tblMain
WHERE ReceiveDate = (SELECT Max(Receivedate) FROM tblMain);

will give you the last record in the record set on the Max of Receiveddate... HTH, Jamie