Rails SELECT Distinct with postgres

Question

So I am trying to use the SELECT Distinct option on my table

Hashlog.select("DISTINCT tag").select("created_at").order("created_at DESC").limit(10)

 1.9.3-p286 :017 > Hashlog.select("DISTINCT tag").select("created_at").order("created_at DESC").limit(10)
      Hashlog Load (0.7ms)  SELECT DISTINCT tag, created_at FROM "hashlogs" ORDER BY created_at DESC LIMIT 10
     => [#<Hashlog tag: "new", created_at: "2012-12-11 04:06:37">, 
     #<Hashlog tag: "now", created_at: "2012-12-11 04:06:33">, 
     #<Hashlog tag: "googleold", created_at: "2012-12-11 04:06:28">, 
     #<Hashlog tag: "google", created_at: "2012-12-11 04:06:26">, 
     #<Hashlog tag: "facebook", created_at: "2012-12-11 04:06:21">, 
     #<Hashlog tag: "facebook", created_at: "2012-12-11 04:06:18">, 
     #<Hashlog tag: "faceboot", created_at: "2012-12-11 04:06:15">]

So I want the results to only unique on the tag column, but it will not let me order by created_at unless its passed through the select.

cdesrosiers · Accepted Answer · 2012-12-11 06:31:59Z

4

I think what you're trying to do is select distinct tag values ordered by the earliest (i.e. smallest) created_at for each tag. That would be one way to resolve the ambiguity resulting from a tag with multiple associated created_at values.

If that's the case, try something like the following:

Hashlog.select("tag, min(created_at) as earliest").group("tag").order("earliest DESC").limit(10)

.

answered Dec 11, 2012 at 6:31

cdesrosiers

8,9122 gold badges30 silver badges33 bronze badges

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

2 Comments

thebusiness11 Over a year ago

Thats great, it worked perfectly. But to go one further, could you explain as to why that worked, but the distinct did not? Not really clear on the usage of group. If not that's okay, I appreciate the answer

cdesrosiers Over a year ago

DISTINCT would have worked fine if you didn't need to sort by created_at. You always need to select a field whenever you want to order by it. GROUP works similarly to DISTINCT in that it picks out unique values from (a) column(s), but it also allows you to select and/or write conditions on their associated rows. In this case, we selected the minimum of created_at among all rows containing a given tag (which form a GROUP for that tag), called it earliest, and sorted the final set of tags by earliest. See this for a better explanation.

freemanoid · Accepted Answer · 2014-05-22 17:25:08Z

0

I have used this following snippet in my project

Product.select("DISTINCT(products.material_id), products.material_id, products.class_id").where('products.class_id' => current_user.class_id)

edited May 22, 2014 at 17:25

freemanoid

14.8k6 gold badges57 silver badges79 bronze badges

answered Dec 11, 2012 at 6:26

rank

837 bronze badges

Collectives™ on Stack Overflow

Rails SELECT Distinct with postgres

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related