what's the generally accepted way of doing this?

[cjard]

suppose i have a table that has 3 columns

postcode1, postcode2, distance

and for every uk address if has the distance between the 2 postcodes. its actually a view that calculates the distance, but thats inconsequential


now suppose i say:

select * from tblPostcodes where postcode1 in ('SW1','SW2') and postcode2 in ('SW3','SW4')

i will get:

SW2 SW4 1.69705627484771405856202646905163769428
SW1 SW3 1.93132079158279658385874750877742036453
SW1 SW4 3.91152144312158922874978293445751064992
SW2 SW3 5.5


now i know why i get 4 results, but i actually only want 2.

i want to group by postcode1, and select the min(distance)
BUT, i do also want to know the pcode2 that relates to these pair!

what's the generally accepted method for doing this? leaving one column out of a grouping expression but showing it infix with its relevant row in the output

clasically i do this with a self join; i select only the pcode1 and the distance using a group, then self join it back to the table, and pull the pcode2:

select *
from
postcodes,
(select min(distance), pcode1 from.. where.. in .. and ... in ... group by ...) finder
where postcodes.pcode1 = finder.pcode1 and postcodes.distance = finder.distance


but its SLOOOOOOW... remember that this isa view, and it seems that when you self join a view in oracle, the time needed rises exponentially. so is there any way? this kind of thing must be needed all the time!

[erickwidya]

i want to group by postcode1, and select the min(distance)
BUT, i do also want to know the pcode2 that relates to these pair! - using correlated subquery

is this
SELECT Table1.postcode1,Table1.postcode2
FROM Table1
WHERE table1.postcode1= "sw1" AND table1.distance <= (SELECT MIN(t2.Distance) AS Distance FROM table1 as t2 WHERE t2.Postcode1 = table1.postcode1)

[cjard]

what's the difference between doing that, and putting the subquery in the FROM area?

SELECT Table1.postcode1,Table1.postcode2
FROM Table1,
(SELECT MIN(t2.Distance) AS Distance FROM table1) t2
WHERE table1.postcode1= "sw1" AND table1.distance <= t2.distance
AND t2.Postcode1 = table1.postcode1

[erickwidya]

Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.PS : which is faster? Correlated or ur last Query(putting subquery in the FROM area)

[cjard]

my assumption would be that my version is faster. in practice, i find the oracle queries in both styles that my company uses, are equally fast.

i presume this is because the oracle sql optimiser does funky things behind the scenes to speed things up...

[cjard]

i personally find it easier to use subsets in the from area, as i can alias each dataset and treat it as a sepparate thing, like a restricted version of the tables i wish to join. its personal preference, i think

select *
from
table1, table2
where
table1.thing = table2.thing and
table1.otherthing in (select athing from table3)

select *
from
table1, table2, (select athing from table3) table3
where
table1.thing = table2.thing and
table1.otherthing = table3.athing


i think oracle compiler produces same compiled code for these

[erickwidya]

if i'm not mistake
select *
from
table1, table2
where
table1.thing = table2.thing and
table1.otherthing in (select athing from table3)

select *
from
table1, table2, (select athing from table3) table3
where
table1.thing = table2.thing and
table1.otherthing = table3.athing if u using IN query, it's using Loop(at least in MSSQL not sure for Oracle) inside to process it..so my opinion the 2nd query faster than the 1st one

but i'm still learning about MSSQL and just happened to saw the 'loop thing' when i see execution plan in QA

maybe Krzemo can give better explanation but i haven't seen him for a while

regards