Off Topic: Need help with SQL Query

[rsbin]

Good Day,

Hoping to get some help with a SQL Query.  My SQL guy recently left, and I don't as yet have an account on any of the other help sites.  So hoping someone here can lend some guidance.  Have the following data from our Engine Telematics Device.  We get readings every hour every day.  Obviously results in a massive SQL Table.  For my PBI Report, I only need the Last or Max reading of each Modified Date for each DeviceID.

DeviceID	ModifiedDate	OBDOdometerMeters	UTCOBDOdometerMetersDate
5599	2/28/2024 23:32	68206300	2/28/2024 20:49
5599	2/28/2024 22:32	68206300	2/28/2024 20:49
5599	2/28/2024 21:32	68206300	2/28/2024 20:49
5606	2/28/2024 23:32	86164000	2/28/2024 23:31
5606	2/28/2024 22:32	86098100	2/28/2024 22:32
5606	2/28/2024 20:32	86096500	2/28/2024 20:03

The View I require should look something like this:

DeviceID	ModifiedDate	OBDOdometerMeters	UTCOBDOdometerMetersDate
5599	2/28/2024 23:32	68206300	2/28/2024 20:49
5606	2/28/2024 23:32	86164000	2/28/2024 23:31

As always, appreciate any support provided.

Kind Regards,

[Daniel29195]

@rsbin 

WITH MaxModifiedDateCTE AS (
SELECT
DeviceID,
ModifiedDate,
OBDOdometerMeters,
UTCOBDOdometerMetersDate,
ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY ModifiedDate DESC ,  UTCOBDOdometerMetersDate desc ) AS RowNum
FROM
YourTableName 
)

SELECT
DeviceID,
ModifiedDate,
OBDOdometerMeters,
UTCOBDOdometerMetersDate
FROM
MaxModifiedDateCTE
WHERE
RowNum = 1;

let me know if this would help .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[rsbin]

@Daniel29195 ,

Trying your query now.   Getting the following red underlines...so doesn't like something:

Do you know where I am going wrong, or what I might be missing?

[Daniel29195 · Accepted solution]

@rsbin 

SELECT 

DeviceID,
ModifiedDate,
OBDOdometerMeters,
UTCOBDOdometerMetersDate

from ( 
SELECT
DeviceID,
ModifiedDate,
OBDOdometerMeters,
UTCOBDOdometerMetersDate,
ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY ModifiedDate DESC ,  UTCOBDOdometerMetersDate desc ) AS RowNum
FROM
YourTableName 
)  as a 

WHERE
RowNum = 1;

the previous should work.  

however you can try this approach ( using nested selected query ) .

let me know if this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

[audreygerred]

Hi, @rsbin! Try this: 

SELECT DeviceID, ModifiedDate, OBDOdometerMeters, UTCOBDOdometerMetersDate
FROM (
SELECT DeviceID, ModifiedDate, OBDOdometerMeters, UTCOBDOdometerMetersDate,
ROW_NUMBER() OVER (PARTITION BY DeviceID ORDER BY ModifiedDate DESC) AS rn
FROM YourTableName
) AS sub
WHERE rn = 1;

[rsbin]

@Daniel29195 , @audreygerred ,

Thank you both for your solutions.  I believe I have the queries working as intended.

Just need to do some additional verification and validation.

Best Regards,

[rsbin]

@Daniel29195 , @audreygerred .

Think I messed up in my Original Post.  Didn't provide enough of a data sample.

Query as tested seems to only provide the record for the max date.  I need the max record for each date.
Attached is a modified sample of data providing for two devices and two dates.  Hence final ouput should be 1 record for each Device and Date ( 4 records).

Would be grateful if either of you can modify your solution to provide what I need.  Again, my apologies for the error in my OP.

Kindest Regards,

[audreygerred]

@rsbin, give this a whirl:

SELECT t.DeviceID, t.ModifiedDate, t.OBDOdometerMeters, t.UTCOBDOdometerMetersDate

FROM your_table_name t

JOIN (

    SELECT DeviceID, CAST(ModifiedDate AS DATE) AS DatePart, MAX(CAST(ModifiedDate AS TIME)) AS MaxTime

    FROM your_table_name

    WHERE ModifiedDate LIKE '% %' -- Filter out records without time

    GROUP BY DeviceID, CAST(ModifiedDate AS DATE)

) AS x

ON t.DeviceID = x.DeviceID AND CAST(t.ModifiedDate AS DATE) = x.DatePart AND CAST(t.ModifiedDate AS TIME) = x.MaxTime

[rsbin]

@audreygerred ,

Thank you kindly for this modification.

Testing it now on the original sql table.  Will let you know how I make out.

Best Regards,

[audreygerred]

Awesome! Good luck!