Select distinct values but order by custom sort

I wrote this query:

select distinct duration from jobs where duration<>'' and not(duration is null) and category='Teaching' order by case when duration='Full-Time' then 1 when duration='Part-Time' then 2 when duration='Long-Term Substitute' then 3 else duration end

But I get the usual:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I have read other posts on this exact topic, but complex SQL is over my head, and I can't understand how to fix this. Even looking at other examples, I am confused. I simply want to sort the results based on those three values.

Thank you.
Brad BansnerWeb DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
No idea, but you could avoid the issue and use a subquery
SELECT duration
FROM (
   select distinct duration 
   from jobs 
   where duration <> '' and not(duration is null) and category='Teaching') a
order by case 
   when duration='Full-Time' then 1 
   when duration='Part-Time' then 2 
   when duration='Long-Term Substitute' then 3 
   else duration end

Open in new window

btw using the return key and tabs to indent will make your T-SQL much more readable.
0
Dany BalianCTOCommented:
try this
select duration from jobs where duration<>'' and not(duration is null) and category='Teaching' 
group by duration 
order by case when duration='Full-Time' then 1 when duration='Part-Time' then 2 when duration='Long-Term Substitute' then 3 else duration end

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brad BansnerWeb DeveloperAuthor Commented:
Works for me, thank you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brad BansnerWeb DeveloperAuthor Commented:
Those both work, actually. "group by" still confuses me, but probably the cleaner way to go?
0
Dany BalianCTOCommented:
yes for this case, both work.. group by could be easier to implement if you have joins with other tables as well, and also cleaner to read (for me)
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> "group by" still confuses me, but probably the cleaner way to go?
As an aside, eyeballeth thy super dooper article  SQL Server GROUP BY Solutions, which is an image and screenshot-heavy tutorial on GROUP BY, and should get you up to speed.

If you like it please hit the 'Yes' button next to 'Vote this article as helpful'?
0
Brad BansnerWeb DeveloperAuthor Commented:
Thanks for the suggestion, I may do just that!
0
Anil GolamariCommented:
SELECT duration FROM Jobs
WHERE duration<>'' and not(duration is null) and category='Teaching'
ORDER BY      
   case when duration='Full-Time' then '1'
   when duration='Part-Time' then '2'
   when duration='Long-Term Substitute' then '3'
    else duration  end ASC
0
Brad BansnerWeb DeveloperAuthor Commented:
All you did was add "asc" to the end?
0
Anil GolamariCommented:
Yeah sorry about that, I try to create a temp table on my local and test it out before i post it. So it took lot of time for me to post. Please ignore my post as solution provided by previous expert will work perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.