Is there a way to SQL ORDER BY column ASCENDING with certain results above the ORDER BY?

Question

I have a 100ish row lookup table that I'm using to generate a vertical check box list on a web page. Very simple query to get the results:

SELECT servicetype FROM XXX

I want to know if there is a way to ORDER BY the column but have certain known values appear on top of the list out of the sort? Something like (and I don't even know how to write this psuedocode but...)

SELECT servicetype FROM XXX
ORDER BY servicetype ASC 
WITH servicetype IN ('Personal', 'Juggling') ON TOP

So that my list would look something like:

Juggling         - On top regardless of ORDER BY
Personal         - On top regardless of ORDER BY
Anteating        - Everything else is ORDER BY
Barflying
Beafeating
Carswatting
etc...

Thanks for the help!

`order by case when servicetype = 'personal' or servicetype = 'juggling' then 1 else 2 end` — Simon, Nov 07 '17 at 17:01

score 5 · Accepted Answer · answered Nov 07 '17 at 16:59

5

You could use:

SELECT servicetype FROM XXX
ORDER BY 
    CASE WHEN servicetype IN ('Personal', 'Juggling') THEN 0 ELSE 1 END ASC
  , servicetype ASC

answered Nov 07 '17 at 16:59

Lukasz Szozda

162,964
23
234
275

score 4 · Answer 2 · answered Nov 07 '17 at 16:59

4

SELECT servicetype 
FROM XXX
order by case when servicetype = 'Juggling' then 'a'
              when servicetype = 'Personal' then 'b'
              else servicetype
         end

answered Nov 07 '17 at 16:59

S3S

24,809
5
26
45

servicetype = 'abc' :) – Lukasz Szozda Nov 07 '17 at 17:00
Fair enough @lad2025 though that'd be highly unexpected from the sample data. – S3S Nov 07 '17 at 17:06

Is there a way to SQL ORDER BY column ASCENDING with certain results above the ORDER BY?

2 Answers2