Adnan Hossain Adnan Hossain - 1 year ago 39
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 Source

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;