Re: Is it possible to make the order of output the same as the order of input parameters?
| От | m. hvostinski |
|---|---|
| Тема | Re: Is it possible to make the order of output the same as the order of input parameters? |
| Дата | |
| Msg-id | AANLkTikxmpK3rlUnPT26ppXqVQOPD-kQZMrUSkSeEi_H@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Is it possible to make the order of output the same as the order of input parameters? (Stephen Frost <sfrost@snowman.net>) |
| Ответы |
Re: Is it possible to make the order of output the same
as the order of input parameters?
Re: Is it possible to make the order of output the same as the order of input parameters? |
| Список | pgsql-general |
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;
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),
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
SELECT * FROM customer
WHERE 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:
Not very easily. My first thought would be doing something like:* 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-----
В списке pgsql-general по дате отправления: