shahgols (5/1/2014)

---

Hi all,

We have a very expensive query that I am tring to rewrite, but the rewritten query (even though is much faster) doesn't return the same number of records. I'm desperate for some help, thank you very much.

Before:

SELECT a.col1

,a.Id AS col2

,(

SELECT col3

FROM (

SELECT e.col3

,ROW_NUMBER() OVER (

PARTITION BY d.col2 ORDER BY d.[col4]

) AS ROWNUMBER

FROM db1.dbo.table1 d(NOLOCK)

INNER JOIN db1.dbo.[table2] e(NOLOCK) ON d.[col4] = e.col5

WHERE d.col2 = a.Id

AND e.col6 = 505

) x

WHERE ROWNUMBER = 1

) AS callcol41

FROM db1.dbo.table2 a(NOLOCK)

After:

SELECT a.col1

,a.Id AS col2

,col3 AS callcol41

FROM (

SELECT e.col3

,ROW_NUMBER() OVER (

PARTITION BY d.col2 ORDER BY d.[col4]

) AS ROWNUMBER

,a.col1

,a.Id

FROM db1.dbo.table1 d(NOLOCK)

INNER JOIN db1.dbo.[table2] e(NOLOCK) ON d.[col4] = e.col5

LEFT OUTER JOIN db1.dbo.table2 a ON d.col2 = a.Id

AND e.col6 = 505

) x

WHERE ROWNUMBER = 1