在Sql Server 2008中使用Distinct＆Order by

I wan to use distinct in a join query with order by. Distinct column and order by columns are different.

我想使用order by在连接查询中使用distinct。不同的列和按列的顺序是不同的。

1 个解决方案

#1

SQL Server only allows you to ORDER BY columns in the SELECT list when DISTINCT is specified as otherwise the non selected column might have more than one value mapping to a particular row in the returned results.

SQL Server仅允许您在指定DISTINCT时在SELECT列表中使用ORDER BY列,否则非选定列可能具有多个映射到返回结果中特定行的值。

If you know for a fact this is not the case you can use a CTE.

如果您知道事实并非如此,您可以使用CTE。

;WITH CTE
     AS (SELECT DISTINCT foo,
                         bar
         FROM   T)
SELECT foo
FROM   CTE
ORDER  BY bar

If this doesn't work for you because there are actually multiple possible bar values for each foo then you need to tell it unambiguously which value to use for ordering purposes. e.g. by using GROUP BY instead.

如果这对你不起作用,因为每个foo实际上有多个可能的bar值,那么你需要明确地告诉它用于排序目的的值。例如通过使用GROUP BY代替。

SELECT foo
FROM T
GROUP BY foo
ORDER BY MIN(bar)

#1