{"id":13297,"date":"2025-08-21T05:25:10","date_gmt":"2025-08-21T05:25:10","guid":{"rendered":"https:\/\/www.bacancytechnology.com\/qanda\/?p=13297"},"modified":"2025-08-21T05:29:22","modified_gmt":"2025-08-21T05:29:22","slug":"use-order_by-and-distinct-true-in-rails-with-postgresql","status":"publish","type":"post","link":"https:\/\/www.bacancytechnology.com\/qanda\/ruby-on-rails\/use-order_by-and-distinct-true-in-rails-with-postgresql","title":{"rendered":"Use order_by expression and distinct: true in Rails with PostgreSQL"},"content":{"rendered":"

Handling order with distinct: true in Rails and PostgreSQL<\/p>\n

Problem Statement
\nWhen using ActiveRecord in Rails with PostgreSQL, you may encounter a common issue when trying to chain `.distinct` with `.order`:<\/p>\n

Model.select(:column).distinct.order(:another_column)<\/h3>\n

This can raise a PostgreSQL error like:<\/p>\n

PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list<\/p>\n

Why the Error Happens<\/h3>\n

PostgreSQL is strict when using DISTINCT. If you use .distinct and specify an ORDER BY, PostgreSQL requires that every column in the ORDER BY must also be selected.<\/p>\n

We want to retrieve unique records, based on some field(s), in a specific order \u2014 without breaking PostgreSQL’s rules.<\/p>\n

Solution 1:<\/strong> Use select to include the order column
\nAdd both the column(s) you want to DISTINCT by and the ones you want to ORDER BY in the select clause:<\/p>\n

User.select('DISTINCT ON (users.email) users.*').order('users.email, users.created_at DESC')<\/code><\/p>\n

Solution 2:<\/strong> Use Subquery to Get Distinct IDs, Then Fetch Full Records<\/p>\n

Step 1:<\/strong> Select distinct IDs in subquery
\ndistinct_ids = User.select(:email).distinct.pluck(:id)<\/code><\/p>\n

Step 2:<\/strong> Use those IDs to load full records with ordering
\nUser.where(id: distinct_ids).order(created_at: :desc)<\/code><\/p>\n

Solution 3:<\/strong> Use DISTINCT ON in Raw SQL for Custom Use
\nIf you need very specific behavior, you can drop to SQL:
\nUser.find_by_sql <<~SQL\n SELECT DISTINCT ON (email) *\n FROM users\n ORDER BY email, created_at DESC\nSQL<\/code><\/p>\n

You\u2019ll get the latest user for each email, sorted by created_at.<\/p>\n

Example Use Case
\nSay you want to get the most recent orders per customer:<\/p>\n

Order.select('DISTINCT ON (customer_id) *').order('customer_id, created_at DESC')<\/code><\/p>\n

\"Also

<\/p>

Also Read:<\/h3>Design Patterns in Rails<\/a><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

Handling order with distinct: true in Rails and PostgreSQL Problem Statement When using ActiveRecord in Rails with PostgreSQL, you may encounter a common issue when trying to chain `.distinct` with `.order`: Model.select(:column).distinct.order(:another_column) This can raise a PostgreSQL error like: PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Why the Error […]<\/p>\n","protected":false},"author":1,"featured_media":13298,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[11],"tags":[],"class_list":["post-13297","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ruby-on-rails"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/posts\/13297"}],"collection":[{"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/comments?post=13297"}],"version-history":[{"count":2,"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/posts\/13297\/revisions"}],"predecessor-version":[{"id":13300,"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/posts\/13297\/revisions\/13300"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/media\/13298"}],"wp:attachment":[{"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/media?parent=13297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/categories?post=13297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bacancytechnology.com\/qanda\/wp-json\/wp\/v2\/tags?post=13297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}