SQL SELECT DISTINCT in Rails ActiveRecord: distinct, DISTINCT ON and the ORDER BY Error
Tested on ActiveRecord 8.1.1 and PostgreSQL 16: the exact SQL, the two errors you will hit, and the fix for each.
SELECT DISTINCT in SQL becomes .distinct in ActiveRecord. SELECT DISTINCT status FROM orders is Order.select(:status).distinct, or Order.distinct.pluck(:status) if you only need the values. The trouble starts when you add a join, an ORDER BY or a “latest row per group” requirement. This post shows the exact SQL Rails generates for each case, the two PostgreSQL errors you will hit, and the fix for each.
All examples ran on ActiveRecord 8.1.1 with PostgreSQL 16.15 on 21 November 2026.
The schema used in every example
class User < ApplicationRecord
has_many :orders
end
class Order < ApplicationRecord
belongs_to :user
end
# orders: user_id, status, total, created_at
SQL DISTINCT to ActiveRecord: the basic cases
| SQL | ActiveRecord |
|---|---|
SELECT DISTINCT status FROM orders |
Order.select(:status).distinct |
SELECT DISTINCT status FROM orders (values only) |
Order.distinct.pluck(:status) |
SELECT COUNT(DISTINCT user_id) FROM orders |
Order.distinct.count(:user_id) |
Check the SQL yourself with .to_sql:
Order.select(:status).distinct.to_sql
# SELECT DISTINCT "orders"."status" FROM "orders"
Order.distinct.pluck(:status)
# SELECT DISTINCT "orders"."status" FROM "orders"
# => ["paid", "refunded"]
Order.distinct.count(:user_id)
# SELECT COUNT(DISTINCT "orders"."user_id") FROM "orders"
Two details worth knowing. pluck returns plain values, so it skips building model objects. And Order.distinct.count with no column counts distinct primary keys: SELECT COUNT(DISTINCT "orders"."id") FROM "orders". That equals a plain count, so it tells you nothing about duplicates.
Do not use .uniq on a relation. In the test it returned an Array, which means Rails loaded every row and removed duplicates in Ruby. The database did no deduplication.
DISTINCT after a JOIN
A join to a has-many table repeats the parent row once per child row. With 2 users and 4 orders:
User.joins(:orders).to_a.size
# SELECT "users".* FROM "users" INNER JOIN "orders" ON "orders"."user_id" = "users"."id"
# => 4
User.joins(:orders).distinct.to_a.size
# SELECT DISTINCT "users".* FROM "users" INNER JOIN "orders" ON "orders"."user_id" = "users"."id"
# => 2
.distinct works here. But if you only need “users who have at least one order”, a subquery is cheaper to reason about and avoids the next error:
User.where(id: Order.select(:user_id)).order(:name)
# SELECT "users".* FROM "users"
# WHERE "users"."id" IN (SELECT "orders"."user_id" FROM "orders")
# ORDER BY "users"."name" ASC
The joins behavior behind this is covered in Rails joins vs includes.
Error 1: ORDER BY expressions must appear in select list
This is a frequent DISTINCT failure on PostgreSQL. You join, deduplicate, and then sort by a column from the joined table:
User.joins(:orders).distinct.order("orders.created_at DESC").to_a
# ActiveRecord::StatementInvalid:
# PG::InvalidColumnReference: ERROR: for SELECT DISTINCT,
# ORDER BY expressions must appear in select list
PostgreSQL cannot sort deduplicated rows by a value that is not in the output. A user with three orders has three possible sort values, and the database cannot pick one for you. Two fixes work.
Fix A: sort by a column that is in the select list, such as users.name, or use the subquery shown above.
Fix B: say which order date you mean with an aggregate:
User.joins(:orders)
.group("users.id")
.order(Arel.sql("MAX(orders.created_at) DESC"))
# SELECT "users".* FROM "users"
# INNER JOIN "orders" ON "orders"."user_id" = "users"."id"
# GROUP BY "users"."id" ORDER BY MAX(orders.created_at) DESC
Grouping by the primary key lets PostgreSQL select every users column. This is the pattern for “users sorted by most recent order”. For more on grouping, see ActiveRecord group and count.
DISTINCT ON: the latest row per group
A common request is “the most recent order for each user”. Plain DISTINCT cannot do this because it compares whole rows. PostgreSQL has DISTINCT ON, which keeps the first row of each group according to your ORDER BY. It is a PostgreSQL feature and is not available in MySQL.
Order.select("DISTINCT ON (user_id) orders.*")
.order(:user_id, created_at: :desc)
# SELECT DISTINCT ON (user_id) orders.* FROM "orders"
# ORDER BY "orders"."user_id" ASC, "orders"."created_at" DESC
With the test data, user 1 has orders of 10, 20 and 5 dollars (the 5 dollar order is the newest), and user 2 has one order of 30. The query returned the newest order for each user: [[1, 5], [2, 30]] as [user_id, total] pairs.
The ORDER BY must start with the DISTINCT ON column. If you leave it out, PostgreSQL rejects the query:
Order.select("DISTINCT ON (user_id) orders.*").order(created_at: :desc).to_a
# PG::InvalidColumnReference: ERROR: SELECT DISTINCT ON expressions
# must match initial ORDER BY expressions
Rails has no built-in distinct_on method, so you pass the clause as a string to select. The string is static, so there is no injection risk here. Never interpolate user input into it.
DISTINCT versus GROUP BY
Both can remove duplicates, and they are not interchangeable:
Order.group(:user_id).maximum(:created_at)
# SELECT MAX("orders"."created_at") AS "maximum_created_at", "orders"."user_id" AS "orders_user_id"
# FROM "orders" GROUP BY "orders"."user_id"
Use distinct to remove duplicate rows. Use group when you want a number per group, such as a max, a count or a sum. Use DISTINCT ON when you want a whole row per group.
Quick reference
| You want | Use |
|---|---|
| Unique values of one column | Model.distinct.pluck(:col) |
| Count of unique values | Model.distinct.count(:col) |
| Parents that have at least one child | Parent.where(id: Child.select(:parent_id)) |
| Newest child per parent (PostgreSQL) | select("DISTINCT ON (parent_id) children.*").order(:parent_id, created_at: :desc) |
| A number per group | group(:col).maximum(:other) |
Not sure how a SQL query maps to ActiveRecord? Paste it into the SQL to ActiveRecord converter and compare the output with .to_sql in your console.
FAQ
How do I write SELECT DISTINCT in Rails?
Call .distinct on the relation. Order.select(:status).distinct generates SELECT DISTINCT "orders"."status" FROM "orders". Use Order.distinct.pluck(:status) to get an array of values.
Why does distinct with order raise PG::InvalidColumnReference?
PostgreSQL requires every ORDER BY expression to appear in the select list of a SELECT DISTINCT query. Sort by a selected column, use a subquery with where(id: ...), or group by the primary key and order by an aggregate.
Is there a distinct_on method in ActiveRecord?
Not in ActiveRecord 8.1.1. Pass select("DISTINCT ON (column) table.*") and order by that column first.
What is the difference between distinct and uniq on a relation?
distinct adds DISTINCT to the SQL. In the test above uniq loaded all rows into an Array and deduplicated them in Ruby.
Does DISTINCT work the same in MySQL?
SELECT DISTINCT works. The ORDER BY error and DISTINCT ON are PostgreSQL specifics, so test your queries on the database you deploy to.
Was this article helpful?
Your feedback helps us improve our content
How We Verify Conversions
Every conversion shown on this site follows a strict verification process to ensure correctness:
- Compare results on same dataset — We run both SQL and ActiveRecord against identical test data and verify results match
-
Check generated SQL with
to_sql— We inspect the actual SQL Rails generates to catch semantic differences (INNER vs LEFT JOIN, WHERE vs ON, etc.) - Add regression tests for tricky cases — Edge cases like NOT EXISTS, anti-joins, and predicate placement are tested with multiple scenarios
- Tested on Rails 8.1.1 — All conversions verified on current Rails version to ensure compatibility
Last updated: November 22, 2026
Try These Queries in Our Converter
See the SQL examples from this article converted to ActiveRecord—and compare the SQL Rails actually generates.
Deep Dive into ActiveRecord
Raza Hussain
Full-stack developer specializing in Ruby on Rails, React, and modern JavaScript. 15+ years upgrading and maintaining production Rails apps. Led Rails 4/5 → 7 upgrades with 40% performance gains, migrated apps from Heroku to Render cutting costs by 35%, and built systems for StatusGator, CryptoZombies, and others. Available for Rails upgrades, performance work, and cloud migrations.
More on PostgreSQL Features in Rails
ActiveRecord where vs find_by — Which to Use When
where returns a relation, find_by returns one record or nil. Here's when each is correct, what breaks if you pick wrong, and Rails 7/8 production patterns.
Rails pick vs pluck: LIMIT 1 in ActiveRecord
pick is pluck for one row. See the SQL for pick vs pluck.first, why ORDER BY matters, and when to use find_by or exists? instead.
ActiveRecord group and count — SQL GROUP BY in Rails
How to use group and count in ActiveRecord for SQL GROUP BY. With having, order, and the SQL Rails generates. Rails 7/8 production examples with output.
Leave a Response
Responses (0)
No responses yet
Be the first to share your thoughts