Ruby-on-rails PG::Error: SELECT DISTINCT, ORDER BY 表达式必须出现在选择列表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12693089/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list
提问by Andy
ActionView::Template::Error (PG::Error: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
ActionView::Template::Error (PG::Error: ERROR: for SELECT DISTINCT, ORDER BY 表达式必须出现在选择列表中
I'm creating an events website and I'm trying to sort the rendered rsvps by the start time of the event. There are a lot of RSVPS so I'm grouping them with distinct, but I've been having a lot of difficulty over the last few days on sorting the results without this error popping up on PG. I've looked at some of the previous questions on the topic and am still pretty lost. How can I get this to work? Thank you so much!
我正在创建一个事件网站，我正在尝试按事件的开始时间对呈现的 rsvps 进行排序。有很多 RSVPS，所以我将它们以不同的方式分组，但是在过去的几天里，我在对结果进行排序时遇到了很多困难，而没有在 PG 上弹出这个错误。我已经查看了有关该主题的一些先前问题，但仍然很迷茫。我怎样才能让它发挥作用？非常感谢！
@rsvps = Rsvp.where(:voter_id => current_user.following.collect {|f| f["id"]}, :status => 'going').where("start_time > ? AND start_time < ?", Time.now, Time.now + 1.month).order("count_all desc").count(:group => :event_id).collect { |f| f[0] }
<%= render :partial => 'rsvps/rsvp', :collection => Rsvp.where(:event_id => @rsvps).select("DISTINCT(event_id)").order('start_time asc') %>
回答by AdrianoKF
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.
我知道这是一个相当古老的问题，但我只是在脑海中经历了一个小例子，它帮助我理解了为什么 Postgres 对 SELECT DISTINCT / ORDER BY 列有这个看似奇怪的限制。
Imagine you have the following data in your Rsvp table:
假设您的 Rsvp 表中有以下数据：
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 1go? Should it come first, because the one tuple starts on Jan 1, 1970, or should it go last because of the Aug 21, 2013?
现在您想要获取不同 event_id 的列表，按它们各自的 start_times 排序。但是应该1去哪里呢？它应该排在第一个，因为一个元组从 1970 年 1 月 1 日开始，还是应该排在最后，因为 2013 年 8 月 21 日？
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_idis unique), we are restricted to ordering only by columns from the SELECTclause.
由于数据库系统无法为您做出决定，并且查询的语法不能依赖于它可能操作的实际数据（假设event_id是唯一的），因此我们只能按SELECT子句中的列进行排序。
As for the actual question - an alternative to Matthew's answer is using an aggregate function like MINor MAXfor the sorting:
至于实际问题 - 马修答案的替代方法是使用聚合函数，如MIN或MAX用于排序：
SELECT event_id
FROM Rsvp
GROUP BY event_id
ORDER BY MIN(start_time)
The explicit grouping and aggregation on start_timepermit the database to come up with a unambiguous ordering of the result tuples. Note however, that readability is definitely an issue in this case ;)
显式分组和聚合start_time允许数据库提出结果元组的明确排序。但是请注意，在这种情况下，可读性绝对是一个问题；)
回答by Matthew Wood
The ORDER BY clause can only be applied afterthe DISTINCT has been applied. Since only the fields in the SELECT statement are taken into consideration for the DISTINCT operations, those are the only fields may be used in the ORDER BY.
ORDER BY 子句只能在应用DISTINCT后应用。因为 DISTINCT 操作只考虑 SELECT 语句中的字段，所以这些是唯一可以在 ORDER BY 中使用的字段。
Logically, if you just want a distinct list of event_id values, what order they occur in should be irrelevant. If order does matter, then you should add the start_time to the SELECT list so that there is context for the order.
从逻辑上讲，如果您只想要一个不同的 event_id 值列表，那么它们出现的顺序应该无关紧要。如果订单确实重要，那么您应该将 start_time 添加到 SELECT 列表中，以便为订单提供上下文。
Also, these two SELECT clauses are NOT equivalent, so be careful:
此外，这两个 SELECT 子句不是等价的，所以要小心：
SELECT DISTINCT(event_id, start_time) FROM ...
SELECT DISTINCT event_id, start_time FROM ...
The second is the form you want. The first will return a series of records with the data represented as a ROW construct (a single column with a tuple inside). The second will return normal columns of data output. It only works as expected in the single-column case where the ROW construct is reduced down since it is only a single column.
第二个是你想要的形式。第一个将返回一系列记录，其数据表示为 ROW 构造（内部带有元组的单个列）。第二个将返回数据输出的正常列。它仅在 ROW 构造减少的单列情况下按预期工作，因为它只是单列。
回答by SUKUMAR S
Because, you are using start_time column, you can use row_number() which is one of Window Functions of PostgreSQLand stack it in
因为，您正在使用 start_time 列，您可以使用 row_number() 这是PostgreSQL的窗口函数之一并将其堆叠
order of start_time, if you are expecting row values with first start_time
Select event_id from (SELECT event_id ,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY start_time) AS first_row FROM Rsvp) where first_row = 1
reverse order of start_time, if you are expecting row values with last start_time
Select event_id from (SELECT event_id ,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY start_time desc) AS last_row FROM Rsvp) where last_row = 1
start_time 的顺序，如果您期望第一个 start_time 的行值
从 (SELECT event_id ,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY start_time) AS first_row FROM Rsvp) 中选择 event_id，其中 first_row = 1
start_time 的相反顺序，如果您期望最后一个 start_time 的行值
从 (SELECT event_id ,ROW_NUMBER() OVER(PARTITION BY event_id ORDER BY start_time desc) AS last_row FROM Rsvp) 中选择 event_id，其中 last_row = 1
You can also use different Window Functionas per your requirements.
您还可以根据您的要求使用不同的窗口函数。
