[PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Craig James 

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?

Thanks,
Craig

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Grzegorz Jaśkiewicz 
On Thu, Jul 9, 2009 at 5:26 PM, Craig Jamescraig_ja...@emolecules.com 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 create translation table, join it, and sort by its key.


-- 
GJ

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Tom Lane 
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= gryz...@gmail.com writes:
 On Thu, Jul 9, 2009 at 5:26 PM, Craig Jamescraig_ja...@emolecules.com 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:

 you can create translation table, join it, and sort by its key.

Much easier to
ORDER BY CASE category WHEN 'z' THEN 1 WHEN 'a' THEN 2 ... END

Actually, consider putting the CASE into a function and doing
ORDER BY sort_order(category)

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Kevin Grittner 
Craig James craig_ja...@emolecules.com 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
 
There was a recent thread discussing ways to do that:
 
http://archives.postgresql.org/pgsql-admin/2009-07/msg00016.php
 
-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Alexander Staubo 
On Thu, Jul 9, 2009 at 6:26 PM, Craig Jamescraig_ja...@emolecules.com 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.

If the object is to avoid a separate table, you can do it with a
case statement:

  select ... from ...
  order by case category
when 1 then 'z'
when 2 then 'a'
when 3 then 'b'
when 4 then 'w'
when 5 then 'h'
  end

If you this sounds slow, you're right. But it might perform well
enough for your use case.

A.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread hubert depesz lubaczewski 
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: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Hartman, Matthew 
 On Thu, Jul 09, 2009 at 09:26:42AM -0700, Craig James wrote:
 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

Another option would be:

select c.*
from categories c
order by case(c.category) when 1 then 'z' when 2 then 'a' then 3 then
'b' when 4 then 'w' when 5 then 'h' end;

Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sorting by an arbitrary criterion

2009-07-09 Thread Grzegorz Jaśkiewicz 
2009/7/9 Tom Lane t...@sss.pgh.pa.us:
 =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= gryz...@gmail.com writes:
 On Thu, Jul 9, 2009 at 5:26 PM, Craig Jamescraig_ja...@emolecules.com 
 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:

 you can create translation table, join it, and sort by its key.

 Much easier to
        ORDER BY CASE category WHEN 'z' THEN 1 WHEN 'a' THEN 2 ... END

 Actually, consider putting the CASE into a function and doing
        ORDER BY sort_order(category)

I suppose table is handy, when you have a lot of items as keys...



-- 
GJ

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance