Search code examples
sql-servert-sqlsubquerycorrelated-subquerycross-apply

Rewrite correlated subquery to CROSS APPLY

So I am trying to use CROSS APPLY but can't seem to get it rigth.

I have some queries that look like this:

SELECT COL1, COL2
FROM dbo.tableA AS A
WHERE COL3 = (SELECT MAX(COL4)
              FROM dbo.tableA AS B
              WHERE A.COL1 = B.COL1) AS SUB

The I try this:

SELECT COL1, COL2
FROM dbo.tableA AS A
CROSS APPLY (SELECT MAX(COL4) AS MAX_DATE
              FROM TABLEA AS B
              WHERE A.COL1 = B.COL1) AS SUB

But I always return more rows when I use the CROSS APPLY. Where is my mistake?

Solution

  • You're missing a WHERE in your second query, if you want the 2 queries to work the same:

    SELECT COL1, COL2
FROM dbo.tableA AS A
CROSS APPLY (SELECT MAX(COL4) AS MAX_DATE
              FROM TABLEA AS B
              WHERE A.COL1 = B.COL1) AS SUB 
WHERE A.COL3 = SUB.MAX_DATE;