group by help... pls

Question

Post reply

group by help... pls

Ram:)

SSCrazy

Points: 2246
More actions
July 29, 2010 at 4:32 pm

#75574

Hi,
we have the query in our sp which is now taking more time
select main.sno,main.name,
(select top 1 address from loca where loca.sno=main.sno)
from main
Now I am planning to use
select main.sno,main.name,max(loca.address) from main,loca
where main.sno=loca.sno
group by main.sno,main.name
Can Anyone please suggest me is it good idea...
FYI... query output count is around 140000
Ram..
🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sumit.agrawal SSCommitted Points: 1616 More actions · Answer 1

Does "loca" table has more than 1 address for each sno...???

Sergiy SSC Guru Points: 110208 More actions · Answer 2

This should be more effective:

select main.sno, main.name, L.address

from main

inner join (

select sno, max(loca.address) address

from loca

group by sno) L ON main.sno = L.sno

But make sure table loca has an index on (sno, address)

_____________
Code for TallyGenerator

Ram:) SSCrazy Points: 2246 More actions · Answer 3

Sumit---

Yes, I may have multiple addresses for each sno.

🙂

Ram:) SSCrazy Points: 2246 More actions · Answer 4

Sergiy (7/29/2010)
This should be more effective:
select main.sno, main.name, L.address
from main
inner join (
select sno, max(loca.address) address
from loca
group by sno) L ON main.sno = L.sno
But make sure table loca has an index on (sno, address)

Hi,

This is effective for me.. But the thing is.. As a have give to queries, Definitely we will get different result as in first statement I am using top 1 and in the second one I am using group by..

Is there any other alternative to the 1st statement with best performance..

Pls suggest me on this

🙂

handkot SSCertifiable Points: 5250 More actions · Answer 5

select top 1 address from loca where loca.sno=main.sno

add order by ...

and queries wiil be identical

I Have Nine Lives You Have One Only
THINK!