PDA

View Full Version : Getting last record for a field



rdl
05-18-2011, 01:35 PM
Hi All,
I have the following table which includes reportvehicleID (autonumber), report reference, vehicle registration number, relation (stolen, recovered) and date of relation.


ReportVehicleID ReportRef RegistrationNo Relation RelationDate
1 4 AAA342 Stolen 2011-02-02
2 5 BBS222 Stolen 2011-02-20
3 4 AAA342 Recovered 2011-05-17


Now I want a list of stolen vehicles i.e. if AAA342 has been recovered I don't want it in the report.
Thus the report would only show the BBS222.

Can someone help me through this pls cause I have been trying a lot.

THANKYOU FOR YOUR HELP.

Regards,
rdl

abduraooft
05-18-2011, 01:46 PM
SELECT ReportVehicleID,RegistrationNo from table_name where
Relation='Stolen' AND
RegistrationNo NOT IN
(SELECT RegistrationNo from table_name where Relation!='Recovered')

PS: I'd use two separate tables to store the two "Relations"

rdl
05-18-2011, 01:55 PM
Thanks for your prompt reply, but query returned 0 results :(

gvre
05-18-2011, 02:53 PM
Try

SELECT ReportVehicleID,RegistrationNo from table_name where
Relation='Stolen' AND
RegistrationNo NOT IN
(SELECT RegistrationNo from table_name where Relation='Recovered')

abduraooft
05-18-2011, 03:06 PM
Try

SELECT ReportVehicleID,RegistrationNo from table_name where
Relation='Stolen' AND
RegistrationNo NOT IN
(SELECT RegistrationNo from table_name where Relation='Recovered')
Yes, that should work. :)

rdl
05-18-2011, 03:14 PM
Table name is reportvehicle....the below worked

SELECT *
FROM reportvehicle A
WHERE A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE B.RegistrationNo = A.RegistrationNo AND
B.Relation = 'Recovered')

Horologe
05-20-2011, 04:38 AM
It would be easier to restructure the table into
ReportVehicleID ReportRef RegistrationNo StolenDate RecoveredDate
1 4 AAA342 2011-02-02 2011-05-17
2 5 BBS222 2011-02-20 0-0-0

bazz
05-20-2011, 01:37 PM
Table name is reportvehicle....the below worked

SELECT *
FROM reportvehicle A
WHERE A.Relation = 'Stolen'
AND NOT EXISTS (SELECT * from reportvehicle B
WHERE B.RegistrationNo = A.RegistrationNo AND
B.Relation = 'Recovered')

yeh but why are you returning all fileds when you don't need to? And also, if you ever add a column to the tbale, that query may break. MUCH better only to query for the columns you need, by name.

bazz