Select distinct values but order by custom sort
Posted on 2014-11-04
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.