Need help for a SELECT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help for a SELECT

Author

Topic

pharvey
Starting Member

20 Posts

Posted - 2007-08-17 : 05:51:16

Hi guys,

There are my tables:
tblVehicleActivity
ID int
VehicleID int
Division varchar
DateIssued datetime
DateReturned datetime
CurrentKm int
ReturnKm int
Purpose varchar
Remarks varchar
Username varchar

tblVehicle
ID int
VehicleNo varchar
Model varchar
CurrentKm int
MaintenanceKm int
Remarks varchar

I want to retrieve the last activity for every Vehicle order by DateReturned. There is my present select which retrieve everything:

SELECT tva.Username, tva.DateReturned, tv.VehicleNo, tv.Model FROM tblVehicleActivity tva
LEFT JOIN tblVehicle tv ON tva.[VehicleId] = tv.[ID]
ORDER BY DateReturned DESC

which return:
Username DateReturned VehicleNo Model
Pascal Harvey 2007-08-16 07:00:00 UN-1715 Prado
Nina Yacoubian 2007-08-16 00:00:00 UN-0535 4 Runner
Pascal Harvey 2007-08-10 15:05:00 UN-0535 4 Runner

But i would like:
Username DateReturned VehicleNo Model
Pascal Harvey 2007-08-16 07:00:00 UN-1715 Prado
Nina Yacoubian 2007-08-16 00:00:00 UN-0535 4 Runner

What would look like my SELECT?

Thanks,
Pascal

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-17 : 06:07:42

well it's just as easy as you think it might be:
at the end of the statement put: ORDER BY tva.DateReturned DESC

Duane.

pharvey
Starting Member

20 Posts

Posted - 2007-08-17 : 06:35:21

Thank for answering but it doesn't solve the problem.

I need to remove the duplicata. If you look into the results I want you should remove the extra UN-0535 vehicle. I want one row per vehicle unique ID(tblVehicle.[ID]). I know about ORDER BY, I would be very shy to publish a post about it ;) lol

Thanks,

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-17 : 07:38:31

Is this what you're looking for? If not, give us some sample data -- some insert statements to populate each table -- and we can help you better

SELECT
tva.Username
,tva.DateReturned
,tv.VehicleNo
,tv.Model
FROM tblVehicleActivity tva

INNER JOIN
(select VehicleID, 'DateReturned' = max(dateReturned)
from tblVehicleActivity
group by VehicleID
) a
ON
a.vehicleId = tva.vehicleID
and a.DateReturned = tva.datereturned
LEFT JOIN
tblVehicle tv
ON tva.[VehicleId] = tv.[ID]
ORDER BY DateReturned DESC

Jim

pharvey
Starting Member

20 Posts

Posted - 2007-08-17 : 09:12:11

Not sure that can work but I found another way to do it:

SELECT tva.*, tv.VehicleNo, tv.Model, tv.MaintenanceKm
FROM tblVehicle tv
RIGHT JOIN tblVehicleActivity tva ON tva.VehicleID = tv.[ID]
WHERE tva.[ID] = (SELECT TOP 1 tempTVA.[ID] FROM tblVehicleActivity tempTVA WHERE tempTVA.VehicleID = tv.[ID] ORDER BY tempTVA.DateReturned)
ORDER BY tva.DateReturned

Thanks Jim it helped me to understand something

johnsql
Posting Yak Master

161 Posts

Posted - 2007-08-17 : 15:29:27

here is a another way to go through the problem

SELECT tva.Username, tva.DateReturned, tv.VehicleNo, tv.Model FROM #tblVehicleActivity tva
INNER JOIN #tblVehicle tv ON tva.[VehicleId] = tv.[ID]
where tva.DateReturned = (select max(DateReturned) from #tblVehicleActivity tva2 where tva2.UserName=tva.UserName)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources