Order By and Distinct on different fields

Hi I’m trying to create a query where I can show different versions and sort by the time inserted, right now I’m having trouble using distinct and order by on different fields and it doesn’t give the correct order

|> distinct([i], i.version)
|> order_by([i], desc: i.inserted_at)

Is there a better way? any help would be appreciated, thank you

Distinct oders by the distinct field, which makes it easier to collapse the resultset to a single value per group.

1 Like

so what should I do If I need to remove version duplication and sort by the time inserted

Version | Inserted_at
1           | 5:00
1           | 5:20
2           | 5:30

A manual group by should do.

Which one you want to keep? The highest or the lowest inserted_at?

FROM table
GROUP BY version

This will keep the maximum inserted_at, if you want to have lowest one then you need to use MIN aggregation function.