Date: Thu, 26 Apr 2007 17:01:11 -0700
To: pgsql-sql@postgresql.org
From: Steve Midgley <public@misuse.org>
Subject: Selecting rows with "static" ordering
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed
Message-Id: <20070427001004.B5F5D9FBABA@postgresql.org>

Hello,

I have a strange problem (or one that I've never had before anyway). I 
am searching for a list of "id's" for a given table (these id values 
are generated at run-time and held statically in an application-local 
variable).

 From that application, I want to retrieve all those rows, and I want 
them in the order they are currently stored in that variable. 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)

Is there a "neat trick" that anyone knows for pulling this off in a 
single query? Basically right now I'm issuing 5 queries to the backend 
to ensure ordering but this horribly inefficient.

Any input or advice would be appreciated,

Steve