Adnan Hossain Adnan Hossain - 6 months ago 11
SQL Question

sorting out the latest filedate in a VIEW in ms sql server

I'm trying to make a view out of three tables in my database.
How it looks

I'm trying to only show the latest filetime that I have. As you can see I have a vehicle that shows two different values depending on the filetime (I have marked that area with a red square).

I would only like to show the latest filetime. How do i do that? I have tried with group on and using max. But it doesn't work. Here is my current code:

SELECT TOP (100) PERCENT MAX(db_ddladmin.Data_Readouts.ReadOutID) AS ReadOutID,
db_ddladmin.Data_Values.Identifier, db_ddladmin.Data_Values.ECU,
MAX(db_ddladmin.Data_Readouts.FileTime) AS FILETIME,
MAX(db_ddladmin.Data_Readouts.FileName) AS FN,
db_ddladmin.Data_Vehicles.Name, db_ddladmin.Data_Values.Value
FROM db_ddladmin.Data_Readouts
INNER JOIN db_ddladmin.Data_Values
ON db_ddladmin.Data_Readouts.ReadOutID = db_ddladmin.Data_Values.ReadOutID
INNER JOIN db_ddladmin.Data_Vehicles
ON db_ddladmin.Data_Readouts.VehicleID = db_ddladmin.Data_Vehicles.ID
GROUP BY db_ddladmin.Data_Values.Identifier, db_ddladmin.Data_Values.ECU,
db_ddladmin.Data_Vehicles.Name, db_ddladmin.Data_Values.Value
ORDER BY MAX(db_ddladmin.Data_Vehicles.Name)


EDIT: The values are from readouts from the vehicle. Why i need the latest filetime is because i want the latest readout. ReadOutID is a number which is given when a readout is made. The higher the number the later the readout is. FN is Filename and it consists the milage and filetime when the readout was made.

Answer

Try this:

SELECT  ReadOutID,
        Identifier, 
        ECU, 
        FileTime, 
        FileName, 
        Name, 
        [Value] 
FROM (
    SELECT  dr.ReadOutID, 
            dv.Identifier, 
            dv.ECU, 
            dr.FileTime, 
            dr.FileName, 
            dve.Name, 
            dv.[Value],
            DENSE_RANK() OVER (PARTITION BY dve.Name ORDER BY dr.FileTime DESC) as drnk
    FROM db_ddladmin.Data_Readouts dr 
    INNER JOIN db_ddladmin.Data_Values dv
        ON dr.ReadOutID = dv.ReadOutID 
    INNER JOIN db_ddladmin.Data_Vehicles dve
        ON dr.VehicleID = dve.ID
    ) as rv
WHERE drnk = 1;
Comments