0
SELECT *
FROM (
   SELECT DISTINCT ON (sec)
          id, sec
   FROM   tasks
   ORDER  BY sec, id DESC
   ) sub
ORDER  BY id DESC
LIMIT  4;

I am wondering if the above SQL query can be converted to an Active Record query. For now I am using find_by_sql as follows:

Task.find_by_sql("SELECT * FROM ( SELECT DISTINCT ON (sec) id, sec FROM tasks ORDER BY sec, id DESC ) sub ORDER BY id DESC LIMIT 4")
user664833
  • 18,397
  • 19
  • 91
  • 140
  • can you give us more information on your table schema? – alalani Apr 08 '14 at 19:02
  • With all due respect, I don't think you need any more info - not because I don't want to share it - but just because I really don't think you need any more info. There is a table called `tasks` (model `Task`) and there are two `integer` columns (`id` and `sec`). You can see both the table creation and the query in [this SQL Fiddle](http://sqlfiddle.com/#!15/06db6/2) - there is an additional function there, which you can ignore. – user664833 Apr 08 '14 at 19:07
  • I was mainly trying to understand the type of the sec column, thanks for letting me know its an integer – alalani Apr 08 '14 at 22:09

1 Answers1

0
Task.select('t.*').from(Task.distinct(:sec).select(:id, :sec).group(:sec).order('sec, id desc'), :t).order('id desc').limit(4)

or just do it with group by only (distinct not needed)

Task.select('t.*').from(Task.select(:id, :sec).group(:sec).order('sec, id desc'), :t).order('id desc').limit(4)

This AR query will split into 2 sql queries, main and sub queries. Sub query will retrieve distinct records on :sec attribute value and main query will order them in descending ids and limit records to 4

Alok Anand
  • 3,346
  • 1
  • 20
  • 17