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 <[email protected]> wrote:
> * m. hvostinski ([email protected]) 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-----
>
>
