如果指定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屋!