Re: ORDER BY using specifc values

On Wed, Apr 12, 2000 at 04:31:18PM -0700, Ian McWilton wrote:
> I need to return the results of a SELECT ordered by a 
> column but in a way that is neither ascending or 
> descending. 
> 
> The ORDER BY column can have one of five values and I 
> want to specify which values are returned in the list 
> in which order.
> 
> An example table below shows the data in the DB:
> 
> INDEX | VALUE
> 1, A
> 2, B
> 3, B
> 4, C
> 5, B

in 6.5.3:

wb_data=> create table x(i int, t text);
CREATE
wb_data=> insert into x values(1,'A');
INSERT 21514 1
wb_data=> insert into x values(2,'C');
INSERT 21515 1
wb_data=> insert into x values(3,'A');
INSERT 21516 1
wb_data=> insert into x values(4,'B');
INSERT 21517 1
wb_data=> select * from x order by t;
i|t
-+-
1|A
3|A
4|B
2|C
(4 rows)

wb_data=> select 
wb_data->   i, t, 
wb_data->   case
wb_data->     when t='C' then 1                                   
wb_data->     when t='A' then 2 
wb_data->     when t='B' then 3 
wb_data->   end as oby                     
wb_data-> from x 
wb_data-> order by oby;
i|t|oby
-+-+---
2|C|  1
1|A|  2
3|A|  2
4|B|  3
(4 rows)
 oliver