Rewrite Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rewrite Query

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2012-03-08 : 05:40:08

Dear Friends,
I have a query like below i want to rewrite query for better performance, in below query the same table is used twice is there any way to elimiate this ?.
select a.col,b.col,c.col,d.col
from table1 a LEFT OUTER JOIN
table2 b
ON a.id = b.id
LEFT OUTER JOIN table3 c
ON b.id = c.id
AND c.id1 = (SELECT MAX (id1)
FROM table3 d
WHERE c.id = d.id)
Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-08 : 07:13:22

Is there an index on table3.id1?

No, you're never too old to Yak'n'Roll if you're too young to die.

shaggy
Posting Yak Master

248 Posts

Posted - 2012-03-08 : 07:58:19

Thanks webfred

There is no index on table3.id1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-08 : 10:20:06

[code]
select a.col,b.col,c.col
from table1 a LEFT OUTER JOIN
table2 b
ON a.id = b.id
LEFT OUTER JOIN
(SELECT
ROW_MUMBER() OVER (PARTITION BY id ORDER BY id1 DESC) AS rn,*
FROM table3) c
ON b.id = c.id
AND c.rn=1[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shaggy
Posting Yak Master

248 Posts

Posted - 2012-03-08 : 23:52:28

Thanks visakh

shaggy
Posting Yak Master

248 Posts

Posted - 2012-03-09 : 00:20:07

visakh i could not rewrite like this, if i join outside the rownumber function will do work for all the records.
select a.col,b.col,c.col
from table1 a LEFT OUTER JOIN
table2 b
ON a.id = b.id
LEFT OUTER JOIN
(SELECT
ROW_MUMBER() OVER (PARTITION BY id ORDER BY id1 DESC) AS rn,*
FROM table3 where b.id = c.id
) c
AND c.rn=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-09 : 11:18:50

quote:
Originally posted by shaggy

visakh i could not rewrite like this, if i join outside the rownumber function will do work for all the records.
select a.col,b.col,c.col
from table1 a LEFT OUTER JOIN
table2 b
ON a.id = b.id
LEFT OUTER JOIN
(SELECT
ROW_MUMBER() OVER (PARTITION BY id ORDER BY id1 DESC) AS rn,*
FROM table3 ~~where b.id = c.id~~
) c
ON b.id = c.id
AND c.rn=1

it should be outside

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources