Thank you All for this extensive help!
BTW: google helps, once you know that the construct is called
correlated subquery - there is no way to get an answer before one
knows the question :)
Thenx again!
-R
On Thu, 2007-06-28 at 23:23 +0530, Gurjeet Singh wrote:
On 6/28/07, Alban Hertroys
It _is_ the optimised version as you can see from the explain plans
posted in the other mail, the planner shows that the cost is drastically
less than the 'distinct on' version.
For smaller data-sets 'distinct-on' version might seem faster, but for
reasonably larger datasets, it's
Gurjeet,
Focusing on the standars solution, I did some 'exercises' - works fine,
just learning.
But the ambarasing thing is, that I looks like I really don't get it,
meaning - what exactly the internal query does. I've never ever seen or
used a subquery with data/params from 'upper level' query
Rafal Pietrak wrote:
Gurjeet,
Focusing on the standars solution, I did some 'exercises' - works fine,
just learning.
But the ambarasing thing is, that I looks like I really don't get it,
meaning - what exactly the internal query does. I've never ever seen or
used a subquery with
On 6/28/07, Alban Hertroys [EMAIL PROTECTED] wrote:
This is called a 'correlated subquery'. Basically the subquery is
performed for each record in the top query.
Google gave me this:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/sqlp/rbafycorrs.htm
I think the
Hi,
I understand, that this is 'general SQL' question rather then 'general
postgres'. But may be someone here could help me with it anyways.
I have a *single* table:
CREATE TABLE test (id int not null unique, thread int not null, info
text);
The ID, although unique, is not continues. A sample
On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote:
Is there an SQL construct to get it?
select
distinct on (t1.id)
t1.*, t2.*
from
test t1
join test t2 on t2.id t1.id
order by t1.id asc, t2.id asc
should do the trick.
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
Now, I'd like to make a JOIN-ed query of that table with itself, so that
I'd get rows paiwise: every row containing data from *two* rows of the
original TEST table so, that those data come from rows of consequtive
ID's - not neceserly (depending on the TEST table contents) continuesly
Marvelous! Thenx!
-R
On Tue, 2007-06-26 at 10:06 +0200, hubert depesz lubaczewski wrote:
On 6/26/07, Rafal Pietrak [EMAIL PROTECTED] wrote:
Is there an SQL construct to get it?
select
distinct on (t1.id)
t1.*, t2.*
from
test t1
join test t2 on t2.id t1.id
Hi Rafal,
Just a note that this is not standard SQL... 'distinct on' is an
extension to SQL provided by postgres.
Following query utilizes the standard SQL to get the same results:
selectt1.id as id, t2.id as id+1,
t1.thread as thread, t2.thread as thread+1,
t1.info as
OK. Have tried this one looks like close to 6 times slower then the
'non-standard' phrase with 'distinct on'.
On the small dataset that I've included in my original post (ten rows of
data within TEST), I've run both queries through EXPLAIN ANALYSE, with
the following result summary (for
I missed the ORDER BY clause... Here it goes:
selectt1.id as id, t2.id as id+1,
t1.thread as thread, t2.thread as thread+1,
t1.info as info, t2.info as info+1
from test as t1, test as t2
where t2.id = ( select min(id) from test as t3 where t3.id t1.id )
order by t1.id asc;
Gurjeet Singh skrev:
I missed the ORDER BY clause... Here it goes:
selectt1.id http://t1.id as id, t2.id http://t2.id as id+1,
t1.thread as thread, t2.thread as thread+1,
t1.info http://t1.info as info, t2.info http://t2.info as
info+1
from test as t1, test as t2
where
news.gmane.org [EMAIL PROTECTED] writes:
Gurjeet Singh skrev:
Also note that this query is much cheaper that the 'distinct on' query
by more than two orders on magnitude ( 217.86 vs. 98040.67):
No it isn't. The estimate is much lower, but the actual times are very
close:
[explain of
I see. (Have actually tried it on a larger dataset - to see it for
myself ... it is optimised :)
Thenx again!
-R
On Tue, 2007-06-26 at 19:56 +0530, Gurjeet Singh wrote:
It _is_ the optimised version as you can see from the explain
plans posted in the other mail, the planner shows
OK, check...
test= CREATE TABLE test (id INTEGER PRIMARY KEY);
test= INSERT INTO test SELECT random()*5 + n*10 FROM
generate_series( 1,10 ) AS n;
test= SELECT * FROM test LIMIT 10;
id
-
11
23
31
41
52
63
70
85
94
103
test= ANALYZE test;
ANALYZE
On 6/26/07, Tom Lane [EMAIL PROTECTED] wrote:
news.gmane.org [EMAIL PROTECTED] writes:
Gurjeet Singh skrev:
Also note that this query is much cheaper that the 'distinct on' query
by more than two orders on magnitude ( 217.86 vs. 98040.67):
No it isn't. The estimate is much lower, but the
17 matches
Mail list logo
