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.