Arel question / ActiveRecord / Windowed SQL queries

I have a table which has repeated entries for the same serial number,
and the date for each row. I want to find the latest record for each
serial number, and I was talking to a DBA about how to do this. He
dictated the following query to me (MS SQL Server):

with latest as (
select ROW_NUMBER() over (partition by DeviceID order by
UTCTimestamp desc) grouping,

  • from Events where EventCode = 0) select * from latest where
    grouping = 1;

Is there a way to write this in Arel? Or should I just use the
‘find_by_sql’ function in ActiveRecord?

My understanding is that the above query creates a temporary table and
populates it with the data from the Events. Each row gets an
ascending integer depending on its order in the sort and the sequence
starts over at 1 every time the DeviceID changes. Then we select just
the rows we want from the temporary table.

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs