PostgreSQL Error for Select Distinct in Rails

Question

On Mysql it works correctly.

PG::Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list LINE 1: ) ORDER BY programs.rating DESC, program_sc... ^ :

Query:

SELECT DISTINCT "programs".* FROM "programs" INNER JOIN "program_schedules" ON "program_schedules"."program_id" = "programs"."id" WHERE (programs.rating >= 5 AND program_schedules.start >= '2012-11-03 23:14:43.457659') AND (ptype = 'movie') ORDER BY programs.rating DESC, program_schedules.start DESC

Rails code:

@data = Program.joins(:program_schedules).where('programs.rating >= ? AND program_schedules.start >= ?',5, Time.now).order('programs.rating DESC, program_schedules.start DESC').uniq

I have tried with

Program.select("programs.*, program_schedules.*).joins(:program_schedules).where(...

but, in this way, when I'm going to read

@data.program_schedules

I get a nil value (When I know there are no nil values)

PostgreSQL 9.2 (Heroku), Ruby 1.9.2

Some info about my DB:

class Program < ActiveRecord::Base
   has_many :program_schedules
end


class ProgramSchedule < ActiveRecord::Base
  belongs_to :program
end

Schema.db

 create_table "program_schedules", :force => true do |t|
        t.integer  "program_id"
        t.datetime "start"
  end

  create_table "programs", :force => true do |t|
    t.string  "title",
    t.string  "ptype"
  end

EDIT: I don't need to order "programs_schedules" because I need all programs_schedules in my array related to that program.

That's a complex enough query where I would just use find_by_sql — n_i_c_k
As always: version of PostgreSQL? Should go without saying for programmers .. Table definitions are never a bad idea either. Would clarify where ptype is from. — Erwin Brandstetter

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2012-11-04T00:11:39

You query is ambiguous in two ways:

ptype is not table-qualified and you did not disclose the table definitions. So the query is ambiguous.

More importantly, you want to:

ORDER BY programs.rating DESC, program_schedules.start DESC

At the same time, however, you instruct PostgreSQL to give you DISTINCT rows from programs. If there are multiple matching rows in program_schedules, how would Postgres know which one to pick for the ORDER BY clause? The first? Last? Earliest, latest, greenest? It's just undefined.

Generally, the ORDER BY clause cannot disagree with the DISTINCT clause, that's what the error message tells you.

Based on a few assumptions, filling in for missing information, your query could look like this:

SELECT p.*
FROM   programs p
JOIN   program_schedules ps ON ps.program_id = p.id
WHERE  p.rating >= 5
AND    ps.start >= '2012-11-03 23:14:43.457659'
AND    p. ptype = 'movie'   -- assuming ptype is from programs  (?)
GROUP  BY p.id              -- assuming it's the primary key
ORDER  BY p.rating DESC, min(ps.start) DESC;  -- assuming smallest start

Also assuming you have PostgreSQL 9.0 or later, which is required for this to work. (Primary key covers whole table in GROUP BY.)

As for:

On Mysql it works correctly.

No it doesn't. It "works", but in mysterious ways rather than "correctly". MySQL allows for all sorts of weird mistakes and goes out of its way (and the SQL standard) to avoid having to throw exceptions - which is a very unfortunate way to deal with errors. It regularly comes back to haunt you later. Demo on Youtube.

Question update

I need all programs_schedules in my array related to that program.

You might want to add:

SELECT p.*, array_agg(ps.start ORDER BY ps.start)

PostgreSQL Error for Select Distinct in Rails

PostgreSQL 9.2 (Heroku), Ruby 1.9.2

1 Answers

Question update