jay jay - 4 years ago 410
SQL Question

Postgres Rails Select Distinct with Order

This seems harder than it should be:

I want to be able to sort a table by it's copy_count, then select only events with a unique title, and limit that query to the first 99.

Event.order("copy_count DESC").select("DISTINCT ON (events.title) *").limit(99)


This throws an error:

ActiveRecord::StatementInvalid: PG::Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions


Which suggest I need to add the copy_count to the DISTINCT ON, but this would also only pull back unique copy_count records which could be the same!

Note: the order by the copy_count MUST happen first.

Thanks

Answer Source

For the pure SQL it will look like:

SELECT *
FROM (SELECT DISTINCT ON (events.title) *
      FROM events
      ORDER BY events.title, events.copy_count DESC) top_titles
ORDER BY events.copy_count DESC
LIMIT 99

But i don't know, how to write it in RoR.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download