OTTIMIZZAZIONE QUERY - SQL SERVER 2000

USE tempdb;
GO

CREATE TABLE dbo.tab1 (Campo1 int, Campo2 int);
CREATE TABLE dbo.tab2 (Campo1 int, Campo2 int, Campo3 int);
GO

INSERT INTO dbo.tab1 VALUES (10, 1003);
INSERT INTO dbo.tab1 VALUES (20, 1123);
INSERT INTO dbo.tab1 VALUES (30, 1345);
INSERT INTO dbo.tab1 VALUES (40, 1456);

INSERT INTO dbo.tab2 VALUES (10, 2322, 11);
INSERT INTO dbo.tab2 VALUES (10, 2112, 22);
INSERT INTO dbo.tab2 VALUES (30, 2563, 33);
INSERT INTO dbo.tab2 VALUES (20, 2879, 44);
GO

/* Query SQL Server 2000 */
SELECT T1.*, Q2.campo3
FROM dbo.tab1 AS T1
LEFT JOIN (
    SELECT T2.*
    FROM dbo.tab2 AS T2
    JOIN (
        SELECT campo1, MAX(campo2) AS campo2
        FROM dbo.tab2
        GROUP BY campo1
    ) AS Q1
    ON T2.campo1 = Q1.campo1
    AND T2.campo2 = Q1.campo2
) AS Q2
ON T1.campo1 = Q2.campo1;

/* Query SQL Server 2005*/
WITH CTE_MAX_campo2 AS
(
    SELECT campo1, MAX(campo2) AS campo2
    FROM dbo.tab2
    GROUP BY campo1
),
CTE_tab2 AS
(
    SELECT T2.*
    FROM dbo.tab2 AS T2
    JOIN CTE_MAX_campo2 AS CTE
    ON T2.campo1 = CTE.campo1
    AND T2.campo2 = CTE.campo2
)
SELECT T1.*, CTE.campo3
FROM dbo.tab1 AS T1
LEFT JOIN CTE_tab2 AS CTE
ON T1.campo1 = CTE.campo1;
GO

/* Output:

campo1      campo2      campo3
----------- ----------- -----------
10          1003        11
20          1123        44
30          1345        33
40          1456        NULL

(4 row(s) affected)

*/

DROP TABLE dbo.tab1, dbo.tab2;