0

Using Oracle 11g

I want a single exception to an otherwise sorted table

select fruit as popluar_choices  
from menu 
order by fruit /* Exception put 'grapefruit' at top of list */

Desired Result

popular_choices
-----------
grapefruit
apple
fig
kiwi
lemon
pear

It's similar to this post: How to apply non standard SQL column sort order?

Community
  • 1
  • 1
zundarz
  • 1,540
  • 3
  • 24
  • 40

3 Answers3

5
  select fruit as popluar_choices  
    from menu 
order by case fruit when 'grapefruit' then 0
                                      else 1
              end,
         fruit
zerkms
  • 249,484
  • 69
  • 436
  • 539
  • 1
    Only ten seconds apart, and the only difference between your answer and mine was that I used 1 and 2 in the case, rather than 0 and 1. Let's go with yours. –  Feb 14 '12 at 21:43
2
SELECT fruit AS popular_choices FROM menu
ORDER BY 
    CASE fruit
        WHEN 'grapefruit' THEN ''
        ELSE fruit
    END
dustyhoppe
  • 1,783
  • 16
  • 20
1
select fruit as popluar_choices  
from menu 
order by CASE fruit = 'grapefruit' THEN '__' ELSE fruit END
Diego
  • 18,035
  • 5
  • 62
  • 66
  • Will fail in case if there is a fruit with name starts with `^` – zerkms Feb 14 '12 at 21:44
  • If any `menu` may have no `fruit` (`fruit` is `''`), your answer will not move grapefruit to the top of the list. –  Feb 14 '12 at 21:45