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

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!

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