Re: ORDER BY using specifc values

From: Oliver Mueschke <o(at)mueschke(dot)de>
To: Ian McWilton <ian(at)blazingcactus(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: ORDER BY using specifc values
Date: 2000-04-13 07:42:30
Message-ID: 20000413094230.B10804@ompc3.dom.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Moray McConnachie 2000-04-13 09:28:26 Re: Subtracting from a date
Previous Message Tom Lane 2000-04-13 07:03:20 Re: ORDER BY using specifc values