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

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! 🤠

9 Replies

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

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

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

      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's avatar
        Daniel29195
        Community Champion
        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! 🤠