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
– n_i_c_k, Commented Nov 3, 2012 at 23:39
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, Commented Nov 4, 2012 at 0:07

Community · Accepted Answer · 2020-06-20 09:12:55Z

7

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)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 4, 2012 at 0:11

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

willglynn Over a year ago

+1! Thanks for the video link in particular. I'm keeping that around for next time I find myself saying the same thing.

sparkle Over a year ago

Ok, thanks. I've updated my question post. Now I'm going to check your query

Collectives™ on Stack Overflow

PostgreSQL Error for Select Distinct in Rails

PostgreSQL 9.2 (Heroku), Ruby 1.9.2

1 Answer 1

Question update

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

PostgreSQL 9.2 (Heroku), Ruby 1.9.2

1 Answer 1

Question update

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related