Re: Is it possible to make the order of output the same as the order of input parameters?

On Wed, Jun 02, 2010 at 03:33:16PM +0100, Sam Mason wrote:
> 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;

How about both, along with a modern JOIN?

WITH
t AS (
VALUES(ARRAY[23, 56, 2, 12, 10])
),
s AS (
SELECT id, row_number() OVER () AS ord
FROM UNNEST((SELECT * FROM t)::int[]) AS r(id)
)
SELECT c.* FROM customer c JOIN s USING(id) ORDER BY s.ord;

And a similar function to the above :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate