Received: from post.webmailer.de (natmail2.webmailer.de [192.67.198.65]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA34583 for ; Thu, 13 Apr 2000 03:46:01 -0400 (EDT) (envelope-from o@mueschke.de) Received: from ompc9.dom100.de (pD4B88D6C.dip.t-dialin.net [212.184.141.108]) by post.webmailer.de (8.9.3/8.8.7) with ESMTP id JAA20266; Thu, 13 Apr 2000 09:45:55 +0200 (MET DST) Received: from ompc3.dom100.de (ompc3.dom.de) [192.168.147.13] (root) by ompc9.dom100.de with esmtp (Exim 2.05 #1 (Debian)) id 12feHx-0000OR-00; Thu, 13 Apr 2000 09:43:46 +0200 Received: (from om@localhost) by ompc3.dom.de (8.9.3/8.8.7) id JAA17575; Thu, 13 Apr 2000 09:42:30 +0200 Date: Thu, 13 Apr 2000 09:42:30 +0200 From: Oliver Mueschke To: Ian McWilton Cc: pgsql-sql@postgresql.org Subject: Re: ORDER BY using specifc values Message-ID: <20000413094230.B10804@ompc3.dom.de> Mail-Followup-To: Ian McWilton , pgsql-sql@postgresql.org References: <200004122331.QAA05037@hedley.blazingcactus.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 1.0us In-Reply-To: <200004122331.QAA05037@hedley.blazingcactus.com>; from ian@blazingcactus.com on Wed, Apr 12, 2000 at 04:31:18PM -0700 X-Archive-Number: 200004/139 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