Ruby-on-Rails: Selecting distinct values from the model

Question

The docs: http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

Clearly state that:

query = Client.select(:name).distinct
# => Returns unique names

However, when I try that in my controller, I get the following error:

undefined method `distinct' for #<ActiveRecord::Relation:0xb2f6f2cc>

To be clear, I want the distinct names, like ['George', 'Brandon'], not the clients actual records. Is there something that I am missing?

That's your Ruby version, which is actually out of date. You should be using 1.9.3 or better. `rails -v` will tell you which version of Rails you're using. — tadman, Aug 01 '13 at 21:12
Another way to select uniq records with Rails you can find [here](https://stackoverflow.com/a/45593677/5872997) — Slava Zharkov, Aug 09 '17 at 14:38

score 50 · Accepted Answer · edited Jul 04 '23 at 15:11

50

Rails 2

If you are still on rails 2 you will need to use:

Client.select('distinct(name)')

Rails 3

If you are on Rails 3 you will need to use:

Client.select(:name).uniq

If you look at the equivalent section of the rails 3 guide you can see the difference between the two versions.

Rails 4+

The .distinct option was added for rails 4 which is what the latest guides refer to.

Client.select(:name).distinct

edited Jul 04 '23 at 15:11

hirowatari

3,195
2
14
15

answered Aug 01 '13 at 21:01

Shadwell

34,314
14
94
99

1

This will fetch all records from db on Rails 3. – Santhosh Feb 18 '16 at 13:22
1

The guide link explicitly states it will do `SELECT DISTINCT name FROM clients`. Yes, it will fetch all distinct names but it will not fetch all records. – Shadwell Feb 18 '16 at 13:24
1

At least on Rails 7, `select + uniq` returns all records to filter then on app, while `select + distinct` uses DISTINCT in query, returning only uniq list to app – Andre Figueiredo Mar 14 '23 at 20:38

score 15 · Answer 2 · answered May 16 '16 at 07:34

15

If you do not want ActiveRecord::Relations returned, just an array of the names as strings, then use:

Client.distinct.pluck(:name)

To get an ordered result set:

Client.order(:name).distinct.pluck(:name)

answered May 16 '16 at 07:34

David Aldridge

51,479
8
68
96

score 13 · Answer 3 · answered Jul 01 '19 at 15:16

13

There are some approaches:

Rails way:
```
Model.select(:name).distinct
```
Semi-rails way
```
Model.select("DISTINCT ON(models.name) models.*")
```
The second allows you to select the first record uniqued by name, but in the whole matter, not only names.

answered Jul 01 '19 at 15:16

Малъ Скрылевъ

16,187
5
56
69

score 4 · Answer 4 · edited Mar 26 '19 at 17:15

4

This will work for Rails 2 (pretty old rails I know!), 3 and 4.

Client.select('distinct(name)')

This will actually use the SQL select distinct statement

SELECT distinct name FROM clients

edited Mar 26 '19 at 17:15

morhook

685
7
19

answered Feb 18 '16 at 13:17

Santhosh

28,097
9
82
87

this won't always work in the case that you to two select statements : scope = Client.select("distinct name"); scope = scope.select("address"); You can end up with the invalid sql : "select address, distinct name from clients" – cmrichards Mar 21 '16 at 11:19
1

Putting `()` around the column solves that problem. Updated my answer. Thanks :) – Santhosh May 16 '16 at 06:52

Ruby-on-Rails: Selecting distinct values from the model

4 Answers4

Rails 2

Rails 3

Rails 4+

Linked

Related