SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SELECT DISTINCT AND ORDER BY CASE


SELECT DISTINCT AND ORDER BY CASE

Author
Message
28.kanikasoni
28.kanikasoni
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 80
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
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19027 Visits: 20461
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
28.kanikasoni
28.kanikasoni
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 80
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'.
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7487 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Phil Parkin
Phil Parkin
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19027 Visits: 20461
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
28.kanikasoni
28.kanikasoni
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 80
Thank you for your excellent solution now its working finew00t
28.kanikasoni
28.kanikasoni
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 80
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
ken.willekens
ken.willekens
Old Hand
Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)Old Hand (366 reputation)

Group: General Forum Members
Points: 366 Visits: 805
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search