Postgres Rails Select Distinct with Order

Question

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

Solution 1

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.

Solution 2

Try this:

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

This happens because when you use the statement DISTINCT ON, you must use its expression (e.g. events.title) in the ORDER BY expressions

SQL ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions 

Thus, you just need to add the copy_count column right after the events.title in the order statement

Solution 3

Try this:

Event.order("copy_count DESC").limit(99).select(:title).uniq

Solution 4

It means that the ORDER BY needs to be "events.title, copy_count DESC". DISTINCT ON requires that the first thing you sort on is the list of columns that are DISTINCT. If you are trying to get the highest result per title, you must group them into sets of rows with the same title first before you can then sort by copy_count. If that's not what you are trying to do, then DISTINCT ON isn't the correct construct.

View more solutions

Comments

[freemanoid]

Would be great to convert it into AR/Arel syntax.

[Todd]

yes the question does specify Rails - anyone know how to specify this with AR Query interface?

[Snowman]

uniq is deprecated and will be removed from Rails 5.1 (use distinct instead)