Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
Stephen Frost 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
Re: [GENERAL] Is it possible to make the order of output the same as the order of input parameters?
* 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?
Sam Mason 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?
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 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?
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?
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 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?
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?
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?
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 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?
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?
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 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?
Tim Landscheidt 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?
Andreas Kretschmer 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?
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?
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? 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?
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?
* 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
[GENERAL] 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