SQL 在列上使用 distinct 并在另一列上执行 order by 会出错

Using distinct on a column and doing order by on another column gives an error

提问by prateek gupta

I have a table: abc_test with columns n_num, k_str.

我有一个表：abc_test，其中包含 n_num、k_str 列。

This query doesnt work:

此查询不起作用：

    select distinct(n_num) from abc_test order by(k_str)

But this one works:

但是这个有效：

    select n_num from abc_test order by(k_str)

How do DISTINCT and ORDER BY keywords work internally that output of both the queries is changed?

DISTINCT 和 ORDER BY 关键字如何在内部工作以更改两个查询的输出？

采纳答案by Abhishek Bhandari

As far as i understood from your question .

据我了解你的问题。

distinct :- means select a distinct(all selected values should be unique). order By :- simply means to order the selected rows as per your requirement .

distinct :- 表示选择一个不同的（所有选定的值都应该是唯一的）。order By :- 只是意味着根据您的要求对选定的行进行排序。

The problem in your first query is For example : I have a table

您的第一个查询中的问题是例如：我有一张表

ID name
01 a
02 b
03 c
04 d 
04 a

now the query select distinct(ID) from table order by (name)is confused which record it should take for ID - 04 (since two values are there,d and a in Name column). So the problem for the DB engine is here when you say order by (name).........

现在查询select distinct(ID) from table order by (name)混淆了 ID - 04 应该采用哪个记录（因为有两个值，d 和 a 在 Name 列中）。所以当你说 order by (name) 时，DB 引擎的问题就在这里.......

回答by Nils Magne Lunde

You might think about using group by instead:

您可能会考虑使用 group by 来代替：

select n_num
from abc_test
group by n_num
order by min(k_str)

回答by Florin Ghita

The first query is impossible. Lets explain this by example. we have this test:

第一个查询是不可能的。让我们通过例子来解释这一点。我们有这个测试：

n_num k_str
2     a
2     c
1     b

select distinct (n_num) from abc_testis

select distinct (n_num) from abc_test是

2
1

Select n_num from abc_test order by k_stris

Select n_num from abc_test order by k_str是

2
1
2

What do you want to return

你想返回什么

select distinct (n_num) from abc_test order by k_str?

select distinct (n_num) from abc_test order by k_str?

it should return only 1 and 2, but how to order them?

它应该只返回 1 和 2，但如何订购它们？

回答by Lukas Eder

How do extended sort key columns

如何扩展排序键列

The logical order of operations in SQLfor your first query, is (simplified):

SQL中第一个查询的逻辑操作顺序是（简化）：

• FROM abc_test
• SELECT n_num, k_stri.e. add a so called extended sort key column
• ORDER BY k_str DESC
• SELECT n_numi.e. remove the extended sort key columnagain from the result.

• FROM abc_test
• SELECT n_num, k_str即添加一个所谓的扩展排序键列
• ORDER BY k_str DESC
• SELECT n_num即再次从结果中删除扩展排序键列。

Thanks to the SQL standard extended sort key columnfeature, it is possible to order by something that is not in the SELECTclause, because it is being temporarily added to it behind the scenes prior to ordering, and then removed again after ordering.

由于 SQL 标准扩展排序键列功能，可以按SELECT子句中没有的内容进行排序，因为它是在排序之前在幕后临时添加到它的，然后在排序后再次删除。

So, why doesn't this work with DISTINCT?

那么，为什么这不适用于DISTINCT？

If we add the DISTINCToperation, it would need to be added between SELECTand ORDER BY:

如果我们添加DISTINCT操作，则需要在SELECT和之间添加ORDER BY：

• FROM abc_test
• SELECT n_num, k_stri.e. add a so called extended sort key column
• DISTINCT
• ORDER BY k_str DESC
• SELECT n_numi.e. remove the extended sort key columnagain from the result.

• FROM abc_test
• SELECT n_num, k_str即添加一个所谓的扩展排序键列
• DISTINCT
• ORDER BY k_str DESC
• SELECT n_num即再次从结果中删除扩展排序键列。

But now, with the extended sort key columnk_str, the semantics of the DISTINCToperation has been changed, so the result will no longer be the same. This is not what we want, so both the SQL standard, and all reasonable databases forbid this usage.

但是现在，随着排序键列的扩展k_str，DISTINCT操作的语义发生了变化，因此结果将不再相同。这不是我们想要的，所以 SQL 标准和所有合理的数据库都禁止这种用法。

Workarounds

解决方法

PostgreSQL has the DISTINCT ONsyntax, which can be used here for precisely this job:

PostgreSQL 有这样的DISTINCT ON语法，它可以在这里精确地用于这项工作：

SELECT DISTINCT ON (k_str) n_num
FROM abc_test
ORDER BY k_str DESC

It can be emulated with standard syntax as follows, if you're not using PostgreSQL

如果您不使用 PostgreSQL，则可以使用以下标准语法对其进行模拟

SELECT n_num
FROM (
  SELECT n_num, MIN(k_str) AS k_str
  FROM abc_test
  GROUP BY n_num
) t
ORDER BY k_str

Or, just simply (in this case)

或者，只是简单地（在这种情况下）

SELECT n_num, MIN(k_str) AS k_str
FROM abc_test
GROUP BY n_num
ORDER BY k_str

I have blogged about SQL DISTINCT and ORDER BY more in detail here.

我在博客上更详细地介绍了 SQL DISTINCT 和 ORDER BY。

回答by P.B.

My query doesn't match yours exactly, but it's pretty close.

我的查询与您的不完全匹配，但非常接近。

select distinct a.character_01 , (select top 1 b.sort_order from LookupData b where a.character_01 = b.character_01 )
from LookupData a 
where 
Dataset_Name = 'Sample' and status = 200
order by 2, 1

回答by Rob van Laarhoven

You are selecting the collection distinct(n_num) from the resultset from your query. So there is no actual relation with the column k_str anymore. A n_num can be from two rows each having a different value for k_str. So you can't order the collection distinct(n_num) by k_str.

您正在从查询的结果集中选择集合 distinct(n_num)。所以与列 k_str 不再有实际关系。n_num 可以来自两行，每行都有不同的 k_str 值。所以你不能通过 k_str 对集合 distinct(n_num) 进行排序。

回答by onedaywhen

According to SQL Standards, a SELECTclause may refer either to as clauses ("aliases") in the top level SELECTclause or columns of the resultset by ordinal position, and therefore nether of your queries would be compliant.

根据 SQL 标准，SELECT子句可能会引用顶级SELECT子句中的 as 子句（“别名”）或按序数位置的结果集列，因此您的查询的任何一个都符合要求。

It seems Oracle, in common with other SQL implemetations, allows you to refer to columns that existed (logically) immediately prior to being projected away in the SELECTclause. I'm not sure whether such flexibility is such a good thing: IMO it is good practice to expose the sort order to the calling application by including the column/expressions etc in the SELECTclause.

与其他 SQL 实现一样，Oracle 似乎允许您引用在SELECT子句中被投影之前（逻辑上）立即存在的列。我不确定这种灵活性是否是一件好事：IMO 通过在SELECT子句中包含列/表达式等来向调用应用程序公开排序顺序是一种很好的做法。

As ever, you need to apply dsicpline to get meaningful results. For your first query, the definition of order is potentially entirely arbitrary.You should be grateful for the error ;)

与以往一样，您需要应用 dsicpline 以获得有意义的结果。对于您的第一个查询，订单的定义可能完全是任意的。您应该对错误表示感谢；)

回答by Shiva N

This approach is available in SQL server 2000, you can select distinct values from a table and order by different column which is not included in Distinct. But in SQL 2012 this will through you an error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified."

这种方法在 SQL Server 2000 中可用，您可以从表中选择不同的值并按不同的列排序，这些列未包含在 Distinct 中。但是在 SQL 2012 中，这将通过您出现错误“如果指定了 SELECT DISTINCT，则 ORDER BY 项目必须出现在选择列表中。”

So, still if you want to use the same feature as of SQL 2000 you can use the column number for ordering(its not recommended in best practice).

因此，如果您想使用与 SQL 2000 相同的功能，您仍然可以使用列号进行排序（在最佳实践中不推荐这样做）。

select distinct(n_num) from abc_test order by 1

This will order the first column after fetching the result. If you want the ordering should be done based on different column other than distinct then you have to add that column also in select statement and use column number to order by.

这将在获取结果后对第一列进行排序。如果您希望排序应该基于不同的列而不是不同的列，那么您还必须在 select 语句中添加该列并使用列号进行排序。

select distinct(n_num), k_str from abc_test order by 2

回答by zengse

you can do

你可以做

select distinct top 10000 (n_num)  --assuming you won't have more than 10,000 rows                
from abc_test order by(k_str)

回答by gjijo

When I got same error, I got it resolved by changing it as

当我遇到同样的错误时，我通过将其更改为

SELECT n_num 
FROM(
   SELECT DISTINCT(n_num) AS n_num, k_str
   FROM abc_test
) as tbl
ORDER BY tbl.k_str