[GENERAL] Is it possible to make the order of output the same as the order of input parameters?

2010-06-02 Thread m. hvostinski 
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

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

2010-06-02 Thread Stephen Frost 
* m. hvostinski (makhv...@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


signature.asc
Description: Digital signature

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

2010-06-02 Thread Sam Mason 
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/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread Andreas Kretschmer 
m. hvostinski makhv...@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°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread A. Kretschmer 
In response to Stephen Frost :
 * m. hvostinski (makhv...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread Tim Landscheidt 
Andreas Kretschmer akretsch...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread Andreas Kretschmer 
Tim Landscheidt t...@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°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread David Fetter 
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 da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread Sam Mason 
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

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

2010-06-02 Thread David Fetter 
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 da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread A. Kretschmer 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread Sam Mason 
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/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread m. hvostinski 
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

(1,23), (2,56),
(3, 2), (4,12),
(5,10)) x(ord,val)
  WHERE c.id = x.val
  ORDER BY x.ord;

== Approach 2 ==

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

== Approach 3 ==

SELECT * FROM customer
WHERE id IN (23, 56, 2, 12, 10)
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;



On Wed, Jun 2, 2010 at 7:43 AM, Stephen Frost sfr...@snowman.net wrote:

 * m. hvostinski (makhv...@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

 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.9 (GNU/Linux)

 iEYEARECAAYFAkwGQ+gACgkQrzgMPqB3kiitUgCgm2kIPIs2eGwfKZCognLUGTqR
 5aMAnRvc/He+Xj/It3eVYNlGIjcUjx8Q
 =OHPl
 -END PGP SIGNATURE-

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

2010-06-02 Thread Yeb Havinga 

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread David Fetter 
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 da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread Tim Landscheidt 
Sam Mason s...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2010-06-02 Thread Stephen Frost 
* Tim Landscheidt (t...@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


signature.asc
Description: Digital signature

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

2010-06-02 Thread Tim Landscheidt 
Stephen Frost sfr...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general