Forum Discussion

rsbin's avatar
rsbin
Community Champion
2 years ago
Solved

Off Topic: Need help with SQL Query

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.  Ha...
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    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's avatar
audreygerred
Super User
2 years ago

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's avatar
rsbin
Community Champion
2 years ago

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's avatar
    audreygerred
    Super User
    2 years ago

    Awesome! Good luck!