Обсуждение: Is it possible to make the order of output the same as the order of input parameters?

Hi,

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?

Thanks

* 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?

Not very easily.  My first thought would be doing something like:

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

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?

Yes, you just need to make the order explicit:

  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;

--
  Sam  http://samason.me.uk/

m. hvostinski <makhvost@gmail.com> wrote:

> Hi,
>
> 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?

No. The only way is:

select * from ... where id in (...) order by case when id=23 then 1,
case when id=56 then 2 end, case when id=2 then 3 end, ...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

In response to Stephen Frost :
> * 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?
>
> Not very easily.  My first thought would be doing something like:
>
> 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

Nice ;-)

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Andreas Kretschmer <akretschmer@spamfence.net> 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?

> No. The only way is:

> select * from ... where id in (...) order by case when id=23 then 1,
> case when id=56 then 2 end, case when id=2 then 3 end, ...

Or, quick 'n' dirty:

| SELECT * FROM customer
|   WHERE id IN (23, 56, 2, 12, 10)
|   ORDER BY POSITION(':' || id || ':' IN ':23:56:2:12:10:');

When using CASE, make sure you read the documentation to the
end: I stumbled upon "CASE id WHEN 23 THEN 1 WHEN 56 THEN 2
WHEN [...] END" only just recently by pure chance :-).

Tim

Tim Landscheidt <tim@tim-landscheidt.de> wrote:

> When using CASE, make sure you read the documentation to the
> end: I stumbled upon "CASE id WHEN 23 THEN 1 WHEN 56 THEN 2
> WHEN [...] END" only just recently by pure chance :-).

Yeah, you are right ;-)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Tue, Jun 01, 2010 at 06:16:06PM -0400, m. hvostinski wrote:
> Hi,
>
> 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;

will give you the indexes along with the elements, and you can then
sort by those.  If you happen to know in advance that you'll only have
integers, you can do this:

CREATE OR REPLACE FUNCTION index_list(integer[])
RETURNS TABLE(i integer, e integer)
LANGUAGE SQL
AS $$
WITH
    t(a) AS (VALUES ($1)),
    s(i) AS (SELECT generate_subscripts((SELECT a FROM t)::integer[], 1))
SELECT i, a[i]
FROM
    s
CROSS JOIN
    t;
$$;

You can then use that set-returning function in your query.

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

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/

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

In response to Sam Mason :
> 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;

Wow, that's really cool and a nice case for row_number().
Thx.

Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

On Wed, Jun 02, 2010 at 04:47:01PM +0200, A. Kretschmer wrote:
> In response to Sam Mason :
> >   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;
>
> Wow, that's really cool and a nice case for row_number().

Just thinking about it now; do SQL's semantics say it'll always do
the right thing?  PG does in a couple of quick tests (i.e. one where
customer is a small table and PG prefers a seqscan and where it's larger
and prefers an index scan) but I'm not sure if this could change.

--
  Sam  http://samason.me.uk/

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    (5,10)) x(ord,val)
  WHERE c.id = x.val
  ORDER BY x.ord;

== Approach 2 ==== Approach 3 ==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;

m. hvostinski wrote:
> 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.
EXPLAIN ANALYZE of these queries in psql should give hard numbers. My
bet is on approach 3 to be the fastest since it does not require a join
node and has shorter target list / memory to move around.

regards
Yeb Havinga

On Wed, Jun 02, 2010 at 11:06:06AM -0400, m. hvostinski wrote:
> 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.

Testing beats theorizing any day.  The array-based approaches are
there pretty much for convenience, i.e. for not having to input the
numbers more than once, as they could easily get mistyped if you need
to repeat them.

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

Sam Mason <sam@samason.me.uk> wrote:

>> >   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;

>> Wow, that's really cool and a nice case for row_number().

> Just thinking about it now; do SQL's semantics say it'll always do
> the right thing?  PG does in a couple of quick tests (i.e. one where
> customer is a small table and PG prefers a seqscan and where it's larger
> and prefers an index scan) but I'm not sure if this could change.

PostgreSQL's documentation on VALUES has at least no guaran-
tee of the order of data. I'd prefer David's solution :-).

Tim

* Tim Landscheidt (tim@tim-landscheidt.de) wrote:
> > Just thinking about it now; do SQL's semantics say it'll always do
> > the right thing?  PG does in a couple of quick tests (i.e. one where
> > customer is a small table and PG prefers a seqscan and where it's larger
> > and prefers an index scan) but I'm not sure if this could change.
>
> PostgreSQL's documentation on VALUES has at least no guaran-
> tee of the order of data. I'd prefer David's solution :-).

Uhm, that's why there's an explicit ORDER BY..  I seriously doubt that
would ever be violated.  If there was an approach suggested which didn't
include an ORDER BY *somewhere*, I'd be suspect of it.

    Thanks,

        Stephen

Stephen Frost <sfrost@snowman.net> wrote:

>> > Just thinking about it now; do SQL's semantics say it'll always do
>> > the right thing?  PG does in a couple of quick tests (i.e. one where
>> > customer is a small table and PG prefers a seqscan and where it's larger
>> > and prefers an index scan) but I'm not sure if this could change.

>> PostgreSQL's documentation on VALUES has at least no guaran-
>> tee of the order of data. I'd prefer David's solution :-).

> Uhm, that's why there's an explicit ORDER BY..  I seriously doubt that
> would ever be violated.  If there was an approach suggested which didn't
> include an ORDER BY *somewhere*, I'd be suspect of it.

The query read:

| 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;

and the question is whether "VALUES (1), (2), (3)" will al-
ways return "(1)", "then" "(2)", "then" "(3)" and whether
"ROW_NUMBER() OVER ()" will always keep that order intact.

Tim