CODEWITHSUNDEEP
sqlruby-on-railspostgresql
jay
jay

Reputation: 305

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

Upvotes: 14

Views: 12289

Answers (4)

Alessandro Gurgel
Alessandro Gurgel

Reputation: 357

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

Upvotes: 8

Matthew Wood
Matthew Wood

Reputation: 16427

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.

Upvotes: 4

edralph
edralph

Reputation: 1831

Try this:

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

Upvotes: 3

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28641

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.

Upvotes: 12

Related Questions