10

My requirement is to get distinct records and in order

User.joins('INNER JOIN report_posts ON posts.id = report_posts.post_id').select('DISTINCT ON (report_posts.post_id) posts.id as report_posts.id as reported_id, report_posts.reported_at').order('report_posts.reported_at desc')

I know this is not possible in postgresql, I already read this Postgresql DISTINCT ON with different ORDER BY

I want its solution that how can I do do this, its alternate way?

Community
  • 1
  • 1
  • Use GROUP BY on all columns to get distinct values. – Pholochtairze May 25 '16 at 10:58
  • @Pholochtairze that's not correct, DISTINCT is the right statement to use. MySQL implements grouping incorrectly, which is probably what causes the confusion. See http://stackoverflow.com/a/1777448/1087866 – Jon Cairns May 25 '16 at 11:15

1 Answers1

10

You need to include the DISTINCT column in your order:

.order('report_posts.post_id, report_posts.reported_at desc')
Jon Cairns
  • 11,783
  • 4
  • 39
  • 66
  • what if I added a `CASE ... AS some_field` to Select section and I'm going to distinct on it? I placed `some_field` to `distinct on` as well as to `select` and `order by` but got the same error – Nickolay Kondratenko Sep 19 '17 at 16:17