On Wed, Jun 02, 2010 at 06:28:14AM -0700, David Fetter wrote:
> On Tue, Jun 01, 2010 at 06:16:06PM -0400, m. hvostinski 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?
>
> Sure, but it can be a little cumbersome to set up at first.
>
> 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;
Isn't this fun; here's another version using window functions (from PG
8.4 onwards) this time:
SELECT c.*
FROM customer c, (
SELECT *, row_number() OVER ()
FROM (VALUES (23), (56), (2), (12), (10)) x) x(val,ord)
WHERE c.id = x.val
ORDER BY x.ord;
--
Sam http://samason.me.uk/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
