styko
April 20, 2020, 6:06am
1
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
styko
April 20, 2020, 6:57am
3
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.