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@xxxxxxxxxx / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
