PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

by

I know this is a rather old question, but I just went through a small example in my head which helped me understand why Postgres has this seemingly odd restriction on SELECT DISTINCT / ORDER BY columns.

Imagine you have the following data in your Rsvp table:

 event_id |        start_time
----------+------------------------
    0     | Mar 17, 2013  12:00:00
    1     |  Jan 1, 1970  00:00:00
    1     | Aug 21, 2013  16:30:00
    2     |  Jun 9, 2012  08:45:00

Now you want to grab a list of distinct event_ids, ordered by their respective start_times. But where should 1 go? Should it come first, because the one tuple starts on Jan 1, 1970, or should it go last because of the Aug 21, 2013?

As the database system can’t make that decision for you and the syntax of the query can’t depend on the actual data it might be operating on (assuming event_id is unique), we are restricted to ordering only by columns from the SELECT clause.

As for the actual question – an alternative to Matthew’s answer is using an aggregate function like MIN or MAX for the sorting:

  SELECT event_id
    FROM Rsvp
GROUP BY event_id
ORDER BY MIN(start_time)

The explicit grouping and aggregation on start_time permit the database to come up with a unambiguous ordering of the result tuples. Note however, that readability is definitely an issue in this case 😉

More Related Contents:

  1. Adding foreign key to a rails model
  2. Is the server running on host “localhost” (::1) and accepting TCP/IP connections on port 5432?
  3. Ignoring time zones altogether in Rails and PostgreSQL
  4. Can’t find the PostgreSQL client library (libpq)
  5. do..end vs curly braces for blocks in Ruby
  6. How to get a random number in Ruby
  7. ActiveRecord: size vs count
  8. Connecting Rails 3.1 with Multiple Databases
  9. In Ruby on Rails, to extend the String class, where should the code be put in?
  10. incompatible character encodings: ASCII-8BIT and UTF-8
  11. STI, one controller
  12. Rails 3, has_one / has_many with lambda condition
  13. Rails update_attributes without save?
  14. Pass a variable into a partial, rails 3?
  15. Installing Postgres on windows for use with Ruby-on-Rails
  16. Using send_file to download a file from Amazon S3?
  17. How to match accented characters with a regex?
  18. Paginating an Array in Ruby with will_paginate
  19. f.error_messages in Rails 3.0
  20. Convert HTML to word file ?
  21. Convert UTC to local time in Rails 3
  22. What does do in rails erb?
  23. Ruby: problem installing EventMachine under Windows 7
  24. Rails Migrations: tried to change the type of column from string to integer
  25. Ruby on Rails 3 howto make ‘OR’ condition
  26. Rails catch-all/globbing routes
  27. Is there a “phpmyadmin” for Ruby on Rails? [closed]
  28. Always getting 401 Unauthorized with new install of Rails + Devise
  29. Rails 2 to Rails 3 : using link_to instead of link_to_remote (including remote and update)
  30. Ruby 1.9.2 and Rails 3 cannot open rails console

Leave a Comment