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

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

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

    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'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,