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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.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