• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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.
0
bbdesign
Asked:
bbdesign
  • 4
  • 2
  • 2
  • +1
1 Solution
 
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
 
bbdesignAuthor Commented:
Works for me, thank you.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bbdesignAuthor 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
 
bbdesignAuthor 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
 
bbdesignAuthor 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now