Re: [GENERAL] Preserving order through an inner join

2010-09-27 Thread Craig Ringer
On 09/27/2010 03:37 AM, Tom Lane wrote: Kevin Jardine writes: I have a query structured like this: SELECT stuff FROM (SELECT more stuff FROM table1 ORDER BY field1) AS q1 INNER JOIN table2 ON ( ... ) and have found that the INNER JOIN is ignoring the order set for q1. The final results ar

Re: [GENERAL] Preserving order through an inner join

2010-09-27 Thread Darren Duncan
Gurjeet Singh wrote: On Sun, Sep 26, 2010 at 9:37 PM, Tom Lane wrote: The SQL standard explicitly disavows any particular output row order unless there is a top-level ORDER BY. (In fact, unless things have changed recently an ORDER BY in a sub-select isn't even legal per spec.)

Re: [GENERAL] Preserving order through an inner join

2010-09-27 Thread Darren Duncan
Following up on other replies, and agreeing that SQL has no inherent row ordering and it is only by accident that you are seeing such ... The *only* way to reliably preserve sort order is by explicitly encoding the order in your data, by maintaining a column or columns through to the outermost

Re: [GENERAL] Preserving order through an inner join

2010-09-27 Thread Gurjeet Singh
On Sun, Sep 26, 2010 at 9:37 PM, Tom Lane wrote: > > The SQL standard explicitly disavows any particular > output row order unless there is a top-level ORDER BY. (In fact, > unless things have changed recently an ORDER BY in a sub-select isn't > even legal per spec.) > > Not sure about the SQL s

Re: [GENERAL] Preserving order through an inner join

2010-09-27 Thread Igor Neyman
> -Original Message- > From: Kevin Jardine [mailto:kevinjard...@yahoo.com] > Sent: Sunday, September 26, 2010 3:15 PM > To: pgsql-general@postgresql.org > Subject: Preserving order through an inner join > > I have a query structured like this: > > SELECT stuff FROM > (SELECT more stuf

Re: [GENERAL] Preserving order through an inner join

2010-09-26 Thread Karsten Hilbert
On Sun, Sep 26, 2010 at 07:32:01PM +, Kevin Jardine wrote: > Hi Pavel, > > I'm not really interested in a "my database is better than your database" > discussion. Pavel did not say that his database is better than yours. What he said was that your query is wrong (with respect to what you s

Re: [GENERAL] Preserving order through an inner join

2010-09-26 Thread Kevin Jardine
OK, then. The sub-select needs to go. Thanks for helpful advice. Kevin --- On Sun, 9/26/10, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] Preserving order through an inner join > To: "Kevin Jardine" > Cc: pgsql-general@postgresql.org > Date: Sunday, Se

Re: [GENERAL] Preserving order through an inner join

2010-09-26 Thread Tom Lane
Kevin Jardine writes: > I have a query structured like this: > SELECT stuff FROM > (SELECT more stuff FROM > table1 > ORDER BY field1) AS q1 > INNER JOIN table2 ON ( ... ) > and have found that the INNER JOIN is ignoring the order set for q1. > The final results are not ordered by field1. Indee

Re: [GENERAL] Preserving order through an inner join

2010-09-26 Thread Kevin Jardine
anyone? Kevin --- On Sun, 9/26/10, Pavel Stehule wrote: > From: Pavel Stehule > Subject: Re: [GENERAL] Preserving order through an inner join > To: "Kevin Jardine" > Cc: pgsql-general@postgresql.org > Date: Sunday, September 26, 2010, 9:23 PM > Hello > > 2010/9

Re: [GENERAL] Preserving order through an inner join

2010-09-26 Thread Pavel Stehule
Hello 2010/9/26 Kevin Jardine : > I have a query structured like this: > > SELECT stuff FROM > (SELECT more stuff FROM > table1 > ORDER BY field1) AS q1 > INNER JOIN table2 ON ( ... ) > > and have found that the INNER JOIN is ignoring the order set for q1. > you can block a hash join that cannot

[GENERAL] Preserving order through an inner join

2010-09-26 Thread Kevin Jardine
I have a query structured like this: SELECT stuff FROM (SELECT more stuff FROM table1 ORDER BY field1) AS q1 INNER JOIN table2 ON ( ... ) and have found that the INNER JOIN is ignoring the order set for q1. The final results are not ordered by field1. This works for other databases (eg. MySQL a