如果指定SELECT DISTINCT，则ORDER BY项必须出现在选择列表中 [英] ORDER BY items must appear in the select list if SELECT DISTINCT is specified

问题描述

我有sql查询，其中我想要返回的行按照特定列的不同的值顺序。

I have sql query in which i want return rows with distinct value order by particular column. like say,i want disntict batchno from ordertable order by locationid.

我已经尝试过google但不能找出解决方案

i have tried google also but not able to find out solution

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

这是我每次尝试的时候。

that what i got every time i tried.

当不使用distinct时，得到输出，但使用此查询重复行

when not using distinct i got output but with duplicate rows using this query

Select Batchno,LocationId from Ordertbl order by case when[LocationId] =3 THEN 0 ELSE 1 END, [LocationId]

Select distinct CAST(Batchno as Varchar(MAX)),LocationId from Ordertbl order by case when[LocationId] =3 THEN 0 ELSE 1 END,[LocationId]

或

Select distinct CAST(Batchno as Varchar(MAX)),LocationId from Ordertbl order by case when[LocationId] =3 THEN 0 ELSE 1 END,[LocationId],[Batchno]

所以这是我试过的东西，但没有运气。

so this is things i tried but no luck.please help me.

推荐答案

DISTINCT 不允许 ORDER BY ，也不在 SELECT 列表中（即使，在这种情况下，表达式只能产生一个特定值对于每个不同的行）。您可以使用。

DISTINCT doesn't allow any expression in the ORDER BY that is not also in the SELECT list (even if, as in this case, the expression could only yield one specific value for each distinct row). You can use.

SELECT Batchno,
       LocationId
FROM   Ordertbl
GROUP  BY Batchno,
          LocationId
ORDER  BY CASE
            WHEN[LocationId] = 3 THEN 0
            ELSE 1
          END,
          [LocationId] 

这篇关于如果指定SELECT DISTINCT，则ORDER BY项必须出现在选择列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！