Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA30987 for ; Thu, 13 Apr 2000 03:03:41 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id DAA09551; Thu, 13 Apr 2000 03:03:20 -0400 (EDT) To: Ian McWilton cc: pgsql-sql@postgresql.org Subject: Re: ORDER BY using specifc values In-reply-to: <200004122331.QAA05037@hedley.blazingcactus.com> References: <200004122331.QAA05037@hedley.blazingcactus.com> Comments: In-reply-to Ian McWilton message dated "Wed, 12 Apr 2000 16:31:18 -0700" Date: Thu, 13 Apr 2000 03:03:20 -0400 Message-ID: <9548.955609400@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200004/138 Ian McWilton writes: > 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 > ...and what I want is for my queries result to be > ordered by VALUE with C put first in the list, then A, > then B. > Having investigated it appears that ORDER BY does not > seem to be any help as it restricts the ordering to ASC > or DESC. Make a function f(x) that converts the stored values into a suitable ordering, say C -> 1, A -> 2, B -> 3. Then do ORDER BY f(column) If you have no more-elegant ideas at hand, f() could be defined using a CASE expression. In fact you could just write the CASE expression right in ORDER BY, but if you need the same ordering in many different queries then defining a function is probably the way to go. regards, tom lane