| Author |
Message |
    
Pda
Celebrity Bewarse
Username: Pda
Post Number: 13722
Registered: 06-2006
Posted From: 70.56.213.83
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 10:39 pm: |    |
ocp ippudu memory test ga ayipoindi mama.. dani nunchi nuvvu peddaga espet seyyamaka..
Don't take any decisions when you are angry. Don't make any promises when you are happy. |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8258
Registered: 03-2004
Posted From: 71.86.125.171
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 10:31 pm: | |
btw .. thx mama ..makes sense .. eppudoo confusion naaku aa part ..
Obeying SpeedLimit!! PS: updated on 08/11 |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8257
Registered: 03-2004
Posted From: 71.86.125.171
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 10:30 pm: | |
vaake .. nuvvu aa test pass avvadam nammadagina vishayame annamaata ayite
Obeying SpeedLimit!! PS: updated on 08/11 |
Pda
Celebrity Bewarse
Username: Pda
Post Number: 13720
Registered: 06-2006
Posted From: 70.56.213.83
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 10:25 pm: | |
oka sare avuddi anukunta as it is not a correlated subquery
Don't take any decisions when you are angry. Don't make any promises when you are happy. |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8254
Registered: 03-2004
Posted From: 71.86.125.171
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 10:19 pm: | |
select A.PK, A.BK, A.col2, A.col3 from Tbl A
where
A.PK in ( select max(B.PK) from Tbl B group by B.BK );
subquery okka saaari execute avudda or ..does it execute once for every record in A ??
Obeying SpeedLimit!! PS: updated on 08/11 |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8245
Registered: 03-2004
Posted From: 209.12.236.10
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 3:51 pm: | |
Gochesh .. so naa select clasue lo 10 columns vunte annintikee aa rank/order logic pettalaa?? chaanallayyindi rank vaadi .. lemme read some theory 
Obeying SpeedLimit!! PS: updated on 08/11 |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8244
Registered: 03-2004
Posted From: 209.12.236.10
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 3:50 pm: | |
420 mama .. inkaa try cheyalaa.. just back from meeting!!
btw
how is select * from tbl where (pk,bk) in (select max(pk),bk from tbl group by bk) this different from select * from tbl where pk in (select max(pk) from tbl group by bk) ?? when I know for sure that pk by itself is unique and has index on it??
is there going to be any performance/cost improvements??
Obeying SpeedLimit!! PS: updated on 08/11 |
420
Kurra Bewarse
Username: 420
Post Number: 2259
Registered: 12-2006
Posted From: 85.144.112.59
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 1:22 pm: | |
Rediff mama .. did you try  |
420
Kurra Bewarse
Username: 420
Post Number: 2253
Registered: 12-2006
Posted From: 85.144.112.59
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 12:25 pm: | |
and create a non unique composite index on PK,BK .. and rock and roll  |
420
Kurra Bewarse
Username: 420
Post Number: 2252
Registered: 12-2006
Posted From: 85.144.112.59
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 12:23 pm: | |
select * from tbl where (pk,bk) in (select max(pk),bk from tbl group by bk)
waset fellass |
Gochi
Bewarse Legend
Username: Gochi
Post Number: 44677
Registered: 07-2004
Posted From: 162.136.192.1
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 12:08 pm: | |
SELECT max(A.PK) keep (dense_rank first order by A.PK desc) ,
max(A.BK) keep (dense_rank first order by A.PK desc),
max(A.Col1) keep (dense_rank first order by A.PK desc),
max(A.Col2) keep (dense_rank first order by A.PK desc)
from A
where ....
idhi try chesi choodu diffesh...performance emanna improve avvacchu.....
Donate Blood Save Life ! Donate Eyes Live Twice  |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8243
Registered: 03-2004
Posted From: 209.12.236.10
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:46 am: | |
SOURCE idi ayite
| PK | BK | c1 | c2 | | 1 | 1 | abc | def | | 2 | 2 | aa | dd | | 3 | 3 | aa | ff | | 4 | 2 | bb | dd | | 5 | 2 | as | sd | | 6 | 6 | asda | adsa |
result idi raavali
| PK | BK | c1 | c2 | | 1 | 1 | abc | def | | 3 | 3 | aa | ff | | 5 | 2 | as | sd | | 6 | 6 | asda | adsa |
Obeying SpeedLimit!! PS: updated on 08/11 |
Nine
Pilla Bewarse
Username: Nine
Post Number: 486
Registered: 04-2005
Posted From: 144.42.9.182
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:46 am: | |
yeah.. my 1st post assumption was incorrect. i see another group by inside which does the job.
index create chesaka.. either 2 or 3 best methods.
gochesh: PK unique key .. BK is natural key.. which has duplicate rows. latest BK ki equalant PK ni match cheyyali. adi diff kochen.
nenu ee patrikaku hater ni kaadu. patrika musugu lo swardha puritha siddanthalanu vallinche vaarante asahyam! |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8242
Registered: 03-2004
Posted From: 209.12.236.10
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:44 am: | |
>>so neeku PK value,max(bk) value and corresponding other columns values for this Max(bk) kaavali...anthenaa
.. tappu .. BK value and other values from record with Max(PK)
Obeying SpeedLimit!! PS: updated on 08/11 |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8241
Registered: 03-2004
Posted From: 209.12.236.10
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:42 am: | |
thx mama .. actual gaa Q2 kooda correct results e istundi ..
inkaa emanna better method vundemo ani aduguthunna .. anthe ..
PK and BK meeda Indexes create chestunnam ..
Obeying SpeedLimit!! PS: updated on 08/11 |
Nine
Pilla Bewarse
Username: Nine
Post Number: 485
Registered: 04-2005
Posted From: 144.42.9.182
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:42 am: | |
hmm.. i guess.. logical ga.. 2nd query kuda equal result return chestundi.. inclusion merge join chesi.
order of cost high to low:
1 > 2 = 3
internal ga 2nd and 3rd same processing logic use chestayi.
create an index on table B (PK, BK) columns for performance improvement.
nenu ee patrikaku hater ni kaadu. patrika musugu lo swardha puritha siddanthalanu vallinche vaarante asahyam! |
Gochi
Bewarse Legend
Username: Gochi
Post Number: 44671
Registered: 07-2004
Posted From: 162.136.192.1
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:41 am: | |
Diffesh,sarigga artham kaaledhu...
Pk is primary key in ur table annavu...daaniki multiple BK's unnayi...so neeku PK value,max(bk) value and corresponding other columns values for this Max(bk) kaavali...anthenaa
Donate Blood Save Life ! Donate Eyes Live Twice |
Ravanabrahma
Yavvanam Kaatesina Bewarse
Username: Ravanabrahma
Post Number: 3841
Registered: 06-2004
Posted From: 65.196.167.82
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:40 am: | |
PK ante primary key ani ardamaindi kani BK endi. backup key naa. naaku theliyadu. evaranna septhe sootha |
Nine
Pilla Bewarse
Username: Nine
Post Number: 484
Registered: 04-2005
Posted From: 144.42.9.182
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:39 am: | |
2nd query incorrect. adi equalent result return cheyyadu. subquery returns the max of the whole table, not the respective key.
1st and 3rd lo queries lo 3rd cost effective.
nenu ee patrikaku hater ni kaadu. patrika musugu lo swardha puritha siddanthalanu vallinche vaarante asahyam! |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8240
Registered: 03-2004
Posted From: 209.12.236.10
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:35 am: | |
also is there any diff between Q2 and Q3 .. in the way how they get executed??
Obeying SpeedLimit!! PS: updated on 08/11 |
Rediff
Mudiripoyina Bewarse
Username: Rediff
Post Number: 8239
Registered: 03-2004
Posted From: 209.12.236.10
Rating:N/A
Votes: 0(Vote!) | | Posted on Tuesday, August 14, 2007 - 11:34 am: | |
maamaloo .. here is my requirement .. there are multiple records in table for given BK value and PK value is unique across table. I need to pull Max(PK), bunch of other cols from corresponding record, for each combination of BK ..
Here are couple of ways to do that .. to the best of my knowledge!!
select A.PK, A.BK, A.col2, A.col3 from Tbl A
where
A.PK in ( select max(B.PK) from Tbl B where B.BK = A.BK );
select A.PK, A.BK, A.col2, A.col3 from Tbl A
where
A.PK in ( select max(B.PK) from Tbl B group by B.BK );
select A.PK, A.BK, A.col2, A.col3 from Tbl A, (select max(B.PK) as PK from Tbl B group by B.BK ) C_MAX_TBL
where A.PK = C_MAX_TBL.PK
e queries cost elaa vuntundi .. which is constlier that the other??
My table has about 2M records and there are about 1.9M unique combinations of BK..
Given this info .. is there any better method??
Obeying SpeedLimit!! PS: updated on 08/11 |
