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

So, you want 99 events.title with highest copy_count without duplicate events.title ? — Ihor Romanchenko
– Ihor Romanchenko, Commented Jan 9, 2013 at 8:34

Ihor Romanchenko · Accepted Answer · 2013-01-09 08:39:59Z

12

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.

answered Jan 9, 2013 at 8:39

Ihor Romanchenko

29k9 gold badges55 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

freemanoid Over a year ago

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

Todd Over a year ago

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

Undo · Accepted Answer · 2015-12-30 18:47:57Z

8

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

edited Dec 30, 2015 at 18:47

Undo♦

25.7k38 gold badges113 silver badges132 bronze badges

answered Dec 30, 2015 at 18:32

Alessandro Gurgel

3574 silver badges8 bronze badges

Comments

Matthew Wood · Accepted Answer · 2013-01-09 20:42:42Z

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.

answered Jan 9, 2013 at 20:42

Matthew Wood

16.5k6 gold badges49 silver badges35 bronze badges

Comments

edralph · Accepted Answer · 2013-01-09 08:40:39Z

3

Try this:

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

answered Jan 9, 2013 at 8:40

edralph

1,8311 gold badge14 silver badges14 bronze badges

1 Comment

Snowman Over a year ago

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

Collectives™ on Stack Overflow

Postgres Rails Select Distinct with Order

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Related