Retreving the last date only | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Retreving the last date only

Hi Everyone… I have a recurring situation where I need to retrieve the latest record. I suspect there is an easy answer Im missing. My table looks like this: CREATE TABLE tblCoachTrackingServiceVehicle (
fldIndex NUMERIC IDENTITY(1,1) CONSTRAINT pk_tblCoachTrackingServiceVehicle_fldIndex PRIMARY KEY,
fldCurrentDate DATETIME DEFAULT GETDATE(),
fldDepartureDate DATETIME,
fldCompany CHAR(2),
fldService CHAR(4),
fldVehicleCode VARCHAR(10)
) And for some sample data:
Index |Current Date |Company |Service |Vehicle |Departure Date 17|2005-12-23 09:48:00.000|MC|411 |435|2005-12-21 00:00:00.000
18|2005-12-23 09:49:00.000|MC|491 |425|2005-12-23 00:00:00.000
19|2005-12-23 09:50:00.000|MC|411 |425|2005-12-21 00:00:00.000
20|2005-12-23 09:51:00.000|MC|493 |403|2005-12-22 00:00:00.000
21|2005-12-23 12:27:00.000|MC|402 |435|2005-12-23 00:00:00.000
22|2005-12-24 02:47:00.000|MC|431 |982|2005-12-24 00:00:00.000
23|2005-12-24 02:49:00.000|MC|431 |658|2005-12-24 00:00:00.000
24|2005-12-24 02:55:00.000|MC|241 |425|2005-12-24 00:00:00.000
25|2005-12-31 10:48:00.000|MC|494 |422|2005-12-31 00:00:00.000
26|2006-01-10 09:15:00.000|MC|321 |422|2006-01-09 00:00:00.000
27|2006-01-10 09:16:00.000|GX|321 |422|2006-01-09 00:00:00.000
28|2006-01-10 09:16:00.000|GX|320 |422|2006-01-10 00:00:00.000
29|2006-01-13 04:16:00.000|GX|442 |435|2006-01-13 00:00:00.000 The goal is to return only the last service information for each vehicle.. ie, for vehicle 425, I would only want to return record 24, not 18 or 19. I cant seem to find sample code for this sort of issue anywhere, its always with dates that it crops up, any help would be appreciated!
Signitures are for people with to much time.
Found an answer, but its not pretty! Can anyone think of a better result?
SELECT A.fldCompany, A.fldService, A.fldVehicleCode, A.fldCurrentDate
FROM viewCoachTrackingServiceVehicle AS A
WHERE A.fldCurrentDate =
(SELECT TOP 1 fldCurrentDate
FROM viewCoachTrackingServiceVehicle AS B
WHERE B.fldVehicleCode = A.fldVehicleCode
ORDER BY fldCurrentDate DESC
) Signitures are for people with to much time.
try this: SELECT A.fldCompany, A.fldService, A.fldVehicleCode, A.fldCurrentDate
FROM viewCoachTrackingServiceVehicle AS A
WHERE A.fldCurrentDate =
(SELECT max(fldCurrentDate)
FROM viewCoachTrackingServiceVehicle AS B
WHERE B.fldVehicleCode = A.fldVehicleCode
)
You can even try this:
But check for the execution plan of all before implementing and test them
SELECT A.fldCompany, A.fldService, A.fldVehicleCode, tmp.mx as fldCurrentDate
FROM viewCoachTrackingServiceVehicle AS A
INNER JOIN
(SELECT fldVehicleCode,max(fldCurrentDate) mx
FROM viewCoachTrackingServiceVehicle
group BY fldVehicleCode
)tmp
ON a.fldVehicleCode =tmp.fldVehicleCode
Hi ranjitjain, The first sample you gave returned this: MC440 4032006-01-18 10:12:00.000
GX490 4252006-01-18 10:13:00.000
GX420 6582006-01-18 10:15:00.000
GX321 4222006-01-10 09:16:00.000
GX320 4222006-01-10 09:16:00.000
GX278 4352006-01-18 10:14:00.000 It fits well and prevents me from using TOP 1, which is good, its also less code. Thanks very much! By the side, dont you have to run a query to see the executation plan? Signitures are for people with to much time.
It’s better to see the execution plan before accepting any changes.
Don’t look whether code is less or not.
Also check the other query as well. To check the plan you have to run the query.
IN QA you can even check the estimated query plan. So to see the right plan which was used in QA goto Query menu and select show plan option and then run your query.
You will see the plan right after result pane.
thanks for the help! Signitures are for people with to much time.
]]>