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@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007