CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresql
thebusiness11
thebusiness11

Reputation: 521

Rails SELECT Distinct with postgres

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.

Upvotes: 1

Views: 4431

Answers (2)

rank
rank

Reputation: 83

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)

Upvotes: 0

cdesrosiers
cdesrosiers

Reputation: 8892

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)

.

Upvotes: 4

Related Questions