Re: Is it possible to make the order of output the same as the order of input parameters? - Mailing list pgsql-general

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/