Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 5050B9FC05E for ; Thu, 26 Apr 2007 21:47:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 76248-02 for ; Thu, 26 Apr 2007 21:46:54 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.246]) by postgresql.org (Postfix) with ESMTP id 110A99FC055 for ; Thu, 26 Apr 2007 21:46:59 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c24so454771ana for ; Thu, 26 Apr 2007 17:46:59 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=NMtJZF8tMdg6BUjNqeoN9pv8gNs6OHhEltie+RaoyOpFU4IAbm2zRFwrz8JHwh+2hz10TZxTYUjx0tw5s+VWBtXGyYq/3dOcB8m6q8bY25yaPT1mjT/AwLi449X3fQw8ha6XtsoSFhNpqLtxObNxl1MOTDWigoMV9WWcpCNS828= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=NjPVCIYkDZ4kDk0yRxEeJys1pSYIbD9QzJ7QyiVKYXo/Zwk6noHFEFHd9BYbS/9RSEaR9Y7LTGKVixP3yIIo8xmY7hnyMorovW3yolmtJBcEC5kwQVDebU6kNjymh+zzeIzrG/jm/3+8FzG/On/RP0eBOyFcNXZxRqCsSmjrHsc= Received: by 10.100.225.13 with SMTP id x13mr1619056ang.1177634818910; Thu, 26 Apr 2007 17:46:58 -0700 (PDT) Received: by 10.100.144.18 with HTTP; Thu, 26 Apr 2007 17:46:58 -0700 (PDT) Message-ID: Date: Thu, 26 Apr 2007 19:46:58 -0500 From: "Aaron Bono" To: "Steve Midgley" Subject: Re: Selecting rows with "static" ordering Cc: pgsql-sql@postgresql.org In-Reply-To: <20070427001004.B5F5D9FBABA@postgresql.org> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_260204_22613583.1177634818787" References: <20070427001004.B5F5D9FBABA@postgresql.org> X-Google-Sender-Auth: d0e295ae76b7a705 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/272 X-Sequence-Number: 28272 ------=_Part_260204_22613583.1177634818787 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 4/26/07, Steve Midgley wrote: > > So take > for example this foreign application variable: > > ids = "3,2,5,1,4" > > The application then executes this sql: > > select * from table where id in (3,2,5,1,4) > > As-is, of course, the above query will return the 5 records in a > semi-random (i.e. unpredictable/unreliable) order. And I don't want to > just "order by id" - I want to "order by id(3,2,5,1,4)" (if you see > what I mean) > > select * from table where id in (3, 2, 5, 1, 4) order by case when (id = 3) then 1 when (id = 2) then 2 when (id = 5) then 3 when (id = 1) then 4 when (id = 4) then 5 else null end ; -- ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_260204_22613583.1177634818787 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 4/26/07, Steve Midgley <public@misuse.org> wrote:

So take
for example this foreign application variable:

ids = "3,2,5,1,4"

The application then executes this sql:

select * from table where id in (3,2,5,1,4)

As-is, of course, the above query will return the 5 records in a
semi-random (i.e. unpredictable/unreliable) order. And I don't want to
just "order by id" - I want to "order by id(3,2,5,1,4)" (if you see
what I mean)

select *
from table
where id in (3, 2, 5, 1, 4)
order by
    case when (id = 3) then 1
    when (id = 2) then 2
    when (id = 5) then 3
    when (id = 1) then 4
    when (id = 4) then 5
    else null
    end
;

--
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_260204_22613583.1177634818787--