pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oliver Mueschke <o@mueschke.de>
To: Ian McWilton <ian@blazingcactus.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: ORDER BY using specifc values
Date: Thu, 13 Apr 2000 09:42:30 +0200
Message-ID: <20000413094230.B10804@ompc3.dom.de> (raw)
In-Reply-To: <200004122331.QAA05037@hedley.blazingcactus.com>
References: <200004122331.QAA05037@hedley.blazingcactus.com>

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



view thread (3+ messages)

Message-ID: <20000413094230.B10804@ompc3.dom.de>
Permalink:  ../20000413094230.B10804@ompc3.dom.de/
Also on:    postgresql.org/message-id/20000413094230.B10804@ompc3.dom.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: o@mueschke.de, ian@blazingcactus.com
  Subject: Re: ORDER BY using specifc values
  In-Reply-To: <20000413094230.B10804@ompc3.dom.de>
  References: <200004122331.QAA05037@hedley.blazingcactus.com> <20000413094230.B10804@ompc3.dom.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox