Skip to content
Advertisement

Removing duplicates from Join with grouped result

I use an sql server with EF Core. Due to lack of GROUP BY support by EF I run raw sql query.

So, I have a history table

Here some Device records can have the same timestamps.

Now I want to have the last state of each device:

I run

but it gives me duplicates (due to timestamp duplicates for a given device, kind of multiple last states of a device. I would prefer to select any of these states)

adding DISTINCT fixes it

but if I want to add a record Id (H.Id) to the final result I obviously get duplicates again.

How could I get some kind of TOP(1) record for each devices?

Advertisement

Answer

I would just use row_number():

This will select exactly one row per device per day. If there are duplicates on the day, it will return an arbitrary value. If you want all of them, then use rank() instead of row_number().

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement