Order by clause 1

[sonname]

I have a table and I want to use a select query to sort the results in ascending order except for one value "other" that I want to always display as the last entry and the rest in alphabetical order. So for example.


Apple
banana
tomoatoes
other


As you can see "O" would come before "T", but this is the exception value that I want to keep at the end of the result set.

Thanks in advance.

 

[clapag22]

select f1, f2, f3,
case
when f1 = 'other' then 'zzz'
else f1 end as sortorder
from tb1
order by 4, 1

 

[sonname]

This sorts out correctly but the problem now is that I see 'zzz' as the result instead of 'other'

 

[MDXer]

The only thing I can think to do is using a case statement as clapag22 demonstrated and concatenate a value or character to 'other' that equates after all string characters.

maybe something like

select f1, f2, f3,
case
when f1 = 'other' then '-Other'
else f1 end as sortorder
from tb1
order by 4, 1

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[SqueakinSweep]

Similar to other posts...but using the sortorder field to store either the dummy value or the field to be sorted

select f1,f2,f3,
sortorder = case rtrim(f1) when 'Other' then 'zzz' else f1 end
from tb1
order by sortorder

Sweep
...if it works dont mess with it