How can I make this query in SQL Server 2005 Compact Edition 3.1?

Question

Hi, this subquery works in SQL Server 2005 Compact Edition 4

 SELECT TableA.serialNo  
 FROM   TableA,
        (SELECT MAX(TableB.lotNo) AS lotNo,TableA.codeA,TableA.codeB
         FROM   TableA, TableB
         WHERE  (TableA.codeA =TableB.codeA)
                AND (TableA.codeB = TableB.codeB)
                AND ((LEN(TableA.openDate) > 0) OR TableA.openDate IS NOT NULL)
         GROUP BY 
                TableA.codeA, TableA.codeB) a  
 WHERE  (TableA.lotNo < a.lotNo)  
        AND (TableA.codeA = a.codeA)  
        AND (TableA.codeB = a.codeB) 
        AND ((LEN(TableA.endDate) = 0) OR TableA.endDate IS NULL)

How can It be made in SQL Server 2005 Compact Edition 3.1?

Thanks!

Answer 1

Have you tried replacing the (less readable imho) join syntax with plain INNER JOINS?

    SELECT  TableA.serialNo   
    FROM    TableA
            INNER JOIN (
              SELECT  MAX(TableB.lotNo) AS lotNo
                      ,TableA.codeA
                      ,TableA.codeB 
              FROM    TableA
                      INNER JOIN TableB ON TableA.codeA = TableB.codeA
                                           AND TableA.codeB = TableB.codeB
              WHERE   LEN(TableA.openDate) > 0
                      OR TableA.openDate IS NOT NULL
              GROUP BY  
                      TableA.codeA, TableA.codeB
            ) a  ON TableA.lotNo < a.lotNo
                    AND TableA.codeA = a.codeA
                    AND TableA.codeB = a.codeB
    WHERE   LEN(TableA.endDate) = 0
            OR TableA.endDate IS NULL

or some rewriting of the conditions

SELECT  a1.serialNo   
FROM    TableA a1
        INNER JOIN (
          SELECT  MAX(b.lotNo) AS lotNo
                  , a.codeA
                  , a.codeB 
          FROM    TableA a
                  INNER JOIN TableB b ON a.codeA = b.codeA
                                         AND a.codeB = b.codeB
          WHERE   a.openDate IS NOT NULL
          GROUP BY  
                  a.codeA, a.codeB
        ) a2  ON a1.lotNo < a2.lotNo
                 AND a1.codeA = a2.codeA
                 AND a1.codeB = a2.codeB
WHERE   LEN(COALESCE(a1.endDate, '')) = 0