Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT DISTINCT AND ORDER BY CASE Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 4:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:11 AM
Points: 16, Visits: 70
Hi i am trying to use select distinct with order by case but getting an error like

[color=#990000]"ORDER BY items must appear in the select list if SELECT DISTINCT is specified."[/color]

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
Post #1379220
Posted Wednesday, October 31, 2012 4:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 5,257, Visits: 12,183
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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1379227
Posted Wednesday, October 31, 2012 10:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:11 AM
Points: 16, Visits: 70
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'.
Post #1379609
Posted Wednesday, October 31, 2012 11:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1379633
Posted Thursday, November 1, 2012 1:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 5,257, Visits: 12,183
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




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1379656
Posted Thursday, November 1, 2012 3:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:11 AM
Points: 16, Visits: 70
Thank you for your excellent solution now its working fine
Post #1379678
Posted Thursday, November 1, 2012 3:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 1:11 AM
Points: 16, Visits: 70
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
Post #1379682
Posted Thursday, November 1, 2012 7:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:20 AM
Points: 285, Visits: 569
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 1 then VacancyInternalID
when 2 then VacancyTitle
else VacancyInternalID
end
)
,OrderBy2= (
case @SortFieldIndexConfig
when 1 then VacancyInternalID
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 '%%'
) sub
ORDER BY sub.OrderBy1 DESC, sub.OrderBy2 DESC

Post #1379795
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse