Re: Sorting by an arbitrary criterion

On Thu, Jul 09, 2009 at 09:26:42AM -0700, Craig James wrote:
> Suppose I have a large table with a small-cardinality CATEGORY column (say, categories 1..5). I need to sort by an arbitrary (i.e. user-specified) mapping of CATEGORY, something like this:
>
> 1 => 'z'
> 2 => 'a'
> 3 => 'b'
> 4 => 'w'
> 5 => 'h'
> So when I get done, the sort order should be 2,3,5,4,1.
> I could create a temporary table with the category-to-key mapping, but is there any way to do this in a single SQL statement?

You can do it like this:

select c.*
from categories c, ( values (1, 'z'), (2, 'a'), (3, 'b'), (4, 'w'), (5, 'h') ) as o (id, ordering) on c.id = o.id
order by o.ordering

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007