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@snowman.net> wrote:
* m. hvostinski (
makhvost@gmail.com) 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?
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-----