Select unique records ordered by date

Question

I have 2 following models:

class Schedule < ActiveRecord::Base    
  attr_accessible :description, :deadline_on, :repeat    
  has_many   :scheduled_transactions    
end

class ScheduledTransaction < ActiveRecord::Base    
  attr_accessible :transaction_on, :description    
  belongs_to :schedule    
end

I want to find last scheduled transactions from each schedule. I know I can do:

Schedule.all.each do |schedule|
  schedule.scheduled_transactions.order(:transaction_on).last
end

but I want to optimize it and make a single database select.

I tried to construct Rails query:

ScheduledTransaction.select('DISTINCT(schedule_id), scheduled_transactions.*').order('transaction_on DESC')

which was translated into:

SELECT DISTINCT(schedule_id), scheduled_transactions.* FROM "scheduled_transactions" ORDER BY transaction_on DESC

but it doesn't give expected results. There are multiple rows with same schedule_id.

And my goal is to select list of scheduled transactions containing last scheduled transaction for each schedule. Any suggestions?

(PostgreSQL) 9.1.9

If you tag the question PostgreSQL please include the *actual SQL* generated by Rails and the result of running it by hand, your PostgreSQL version, any relevant error messages, etc. — Craig Ringer, Apr 19 '13 at 09:06
Hi Craig, I have added required information to the question. Thanks for a tip. — user882027, Apr 19 '13 at 09:52
The [DISTINCT ON](http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT) clause could be what you want? — Guedes, Apr 19 '13 at 11:00

lassej · Accepted Answer · 2013-04-19T11:31:11.447

1

As Guedes writes, you can use DISTINCT ON for this:

SELECT DISTINCT ON (schedule_id) *
  FROM scheduled_transactions
  ORDER BY schedule_id, transaction_on DESC

edited Apr 19 '13 at 11:31

answered Apr 19 '13 at 11:13

lassej

6,256
6
26
34

Select unique records ordered by date

1 Answers1