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

table
|> 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?

SELECT
  MAX(inserted_at),
  version
FROM table
GROUP BY version
ORDER BY 1

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