为什么我不能在SQL中同时使用ORDER BY和DISTINCT*

Question

我正在尝试执行以下操作，如果取消对distinct的注释，它将中断。此外，如果我注释掉顺序，并将distinct留在中，它将起作用。

Contestant.joins('INNER JOIN votes AS V ON V.contestant_id = contestants.id AND V.season_id = '+ season_number.to_s)
                      .joins('LEFT OUTER JOIN votes AS XV ON (XV.contestant_id = '+self.id.to_s+') AND (XV.tribal_council_key = V.tribal_council_key) AND XV.contestant_voted_for_id = V.contestant_voted_for_id')
                      .joins('INNER JOIN season_rosters ON season_rosters.season_id = V.season_id')
                      .where('V.is_jury_vote = (?) AND V.contestant_id <> (?) AND XV.tribal_council_key IS NOT NULL', :false, self.id)
                      .order('season_rosters.finished')
                      #.distinct

我得到的错误如下。。。

TinyTds::Error: Incorrect syntax near '*'.: EXEC sp_executesql N'SELECT DISTINCT *, __order FROM ( SELECT [contestants].*, DENSE_RANK() OVER (ORDER BY season_rosters.finished ASC) AS __order, ROW_NUMBER() OVER (PARTITION BY [contestants].* ORDER BY season_rosters.finished ASC) AS __joined_row_num FROM [contestants] INNER JOIN votes AS V ON V.contestant_id = contestants.id AND V.season_id = 6 LEFT OUTER JOIN votes AS XV ON (XV.contestant_id = 112) AND (XV.tribal_council_key = V.tribal_council_key) AND XV.contestant_voted_for_id = V.contestant_voted_for_id INNER JOIN season_rosters ON season_rosters.season_id = V.season_id WHERE (V.is_jury_vote = (''false'') AND V.contestant_id <> (112) AND XV.tribal_council_key IS NOT NULL) ) AS __sq WHERE __joined_row_num = 1 ORDER BY __order'

Answer 1

问题出在这部分：

SELECT DISTINCT *, __order

尝试将所需列添加到GROUP BY.

Contestant.joins('INNER JOIN votes AS V ON V.contestant_id = contestants.id AND V.season_id = '+ season_number.to_s)
                      .joins('LEFT OUTER JOIN votes AS XV ON (XV.contestant_id = '+self.id.to_s+') AND (XV.tribal_council_key = V.tribal_council_key) AND XV.contestant_voted_for_id = V.contestant_voted_for_id')
                      .joins('INNER JOIN season_rosters ON season_rosters.season_id = V.season_id')
                      .where('V.is_jury_vote = (?) AND V.contestant_id <> (?) AND XV.tribal_council_key IS NOT NULL', :false, self.id)
                      .order('season_rosters.finished')
                      .group('col1,col2,__order')

同样在SQL错误中，order by在另一列上，而在代码中，它在season_rosters.finished上。