Re: Is it possible to make the order of output the same as the order of input parameters?

["m. hvostinski" <makhvost@xxxxxxxxx>]

Thanks to all for the feedback. I keep getting impressed by how flexible
 PostgreSQL is.

Any ideas which query should perform better? I 
put together all the suggested approaches below.


== Approach 1
 ==
SELECT c.*
  FROM customer c, (VALUES

    (1,23), (2,56),
    (3, 2), (4,12),

    (5,10)) x(ord,val)
  
WHERE c.id = x.val
  ORDER 
BY x.ord;

== Approach 2 ==

SELECT
  customer.*
FROM
  customer a
  JOIN (VALUES (1,23),(2,56),(3,2),(4,12),(5,10)) b
    ON (a.id = b.column2)
ORDER BY b.column1

== Approach 3 ==

SELECT * FROM customer
WHERE id IN (23, 56, 2, 12, 10)

ORDER BY POSITION(':' || id || 
':' IN ':23:56:2:12:10:');

== Approach 4 ==
WITH
   
 t(a) AS (VALUES (ARRAY[23, 56, 2, 12, 10])),
    s(i) AS (SELECT 
generate_subscripts((SELECT a FROM t)::integer[], 1))
SELECT i, a[i]
FROM s CROSS JOIN t;

On Wed, Jun 2, 2010 at 7:43 AM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote:

* m. hvostinski (makhvost@xxxxxxxxx) wrote:
> I have a simple query like:
>
> SELECT * FROM customer WHERE id IN (23, 56, 2, 12, 10)
>
> The problem is that I need to retrieve the rows in the same order as the set
> of ids provided in the select statement. Can it be done?

Not very easily.  My first thought would be doing something like:

SELECT
 customer.*
FROM
 customer a
 JOIN (VALUES (1,23),(2,56),(3,2),(4,12),(5,10)) b
   ON (a.id = b.column2)
ORDER BY b.column1
;

       Thanks,

               Stephen

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkwGQ+gACgkQrzgMPqB3kiitUgCgm2kIPIs2eGwfKZCognLUGTqR
5aMAnRvc/He+Xj/It3eVYNlGIjcUjx8Q
=OHPl
-----END PGP SIGNATURE-----