October 31, 2012 at 4:01 am
Hi i am trying to use select distinct with order by case but getting an error like
"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."
select DISTINCT VacancyInternalID , VacancyTitle ,NULL ParentInternalID , NULL GeoLocation from Vacancy.TB_Vacancy va inner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalID where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647' and co.ContractStatusID = 1 and VacancyStatusID = 1 and VacancyTitle like '%%' order by case when @SortFieldIndexConfig=1 then VacancyInternalID end DESC, case when @SortFieldIndexConfig=2 then VacancyTitle end DESC,case when @SortFieldIndexConfig NOT IN(1,2) then VacancyInternalID end DESC
I want a solution without subquery for this problem.can any one help on this topic
October 31, 2012 at 4:25 am
Just add the CASE construct to your SELECT. The CASE construct can be made more compact too:
select distinct VacancyInternalID
,VacancyTitle
,null ParentInternalID
,null GeoLocation
,
order = (
case @SortFieldIndexConfig
when 2
then VacancyTitle
else VacancyInternalID
end
)
from Vacancy.TB_Vacancy va
inner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalID
where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647'
and co.ContractStatusID = 1
and VacancyStatusID = 1
and VacancyTitle like '%%'
order by (
case @SortFieldIndexConfig
when 2
then VacancyTitle
else VacancyInternalID
end
) desc
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 31, 2012 at 10:15 pm
Hi,
Thanks 4 ur solution but I am getting error in order clause
Incorrect syntax near the keyword 'order'.
Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near the keyword 'ELSE'.
October 31, 2012 at 11:52 pm
28.kanikasoni (10/31/2012)
Hi,Thanks 4 ur solution but I am getting error in order clause
Incorrect syntax near the keyword 'order'.
Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near the keyword 'ELSE'.
Try removing the parentheses around the CASE statement in the ORDER BY clause.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 1, 2012 at 1:58 am
dwain.c (10/31/2012)
28.kanikasoni (10/31/2012)
Hi,Thanks 4 ur solution but I am getting error in order clause
Incorrect syntax near the keyword 'order'.
Incorrect syntax near the keyword 'ELSE'.
Incorrect syntax near the keyword 'ELSE'.
Try removing the parentheses around the CASE statement in the ORDER BY clause.
Actually, I think the problem might be that I used the reserved word 'Order' as column alias. Try this:
select distinct VacancyInternalID
,VacancyTitle
,null ParentInternalID
,null GeoLocation
,
OrderBy = (
case @SortFieldIndexConfig
when 2
then VacancyTitle
else VacancyInternalID
end
)
from Vacancy.TB_Vacancy va
inner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalID
where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647'
and co.ContractStatusID = 1
and VacancyStatusID = 1
and VacancyTitle like '%%'
order by (
case @SortFieldIndexConfig
when 2
then VacancyTitle
else VacancyInternalID
end
) desc
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 1, 2012 at 3:06 am
Thank you for your excellent solution now its working fine:w00t:
November 1, 2012 at 3:16 am
But here I am getting one extra column which I don't want.
and also in other cases I need to use multiple case statement with order by because different datatype of columns create problem in single case statement with multiple whens.
Plz provide solution to this prob
November 1, 2012 at 7:16 am
You could use Phil's suggested query as a subquery. This lets you add as many CASE statements with as many WHEN statements as you want and it lets you filter out those unwanted columns in the final query result. The following code should give you an idea of what I mean. Obviously the OrderBy2 part would not have the same syntax as OrderBy1.
SELECT
sub.VacancyInternalID
,sub.VacancyTitle
,sub.ParentInternalID
,sub.GeoLocation
FROM
(
select distinct VacancyInternalID
,VacancyTitle
,null ParentInternalID
,null GeoLocation
,OrderBy1= (
case @SortFieldIndexConfig
when 1then VacancyInternalID
when 2then VacancyTitle
else VacancyInternalID
end
)
,OrderBy2= (
case @SortFieldIndexConfig
when 1then VacancyInternalID
when 2then VacancyTitle
else VacancyInternalID
end
)
from Vacancy.TB_Vacancy va
inner join Config.TB_Contract co on va.VacancyContractID = co.ContractInternalID
where co.ContractExternalID = '492A94D0-7D71-46E5-A8F6-E3A973394647'
and co.ContractStatusID = 1
and VacancyStatusID = 1
and VacancyTitle like '%%'
) sub
ORDER BY sub.OrderBy1 DESC, sub.OrderBy2 DESC
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply