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