ORA-00905: Trouble converting Mysql/MMSQL syntax to oracle syntax

Question

Hi guys i'm having trouble running the below code on an Oracle DB not too sure why - getting ORA-905 error - missing keyword

This works fine on MSSQL and MYSQL.

Any indication as to how to fix will be much appreciated.

Cheers.

SELECT product.productId, product.version
 FROM product 
INNER JOIN (SELECT productId,
                   MAX(version) AS MaxVersion
              FROM product
          GROUP BY productId) AS vTbl ON product.productId= vTbl.productId 
                                     AND product.version= vTbl.MaxVersion

Answer 1

SELECT
  product.productId, product.version
FROM
    product JOIN
    (
            SELECT
                    productId,
                    MAX(version) AS MaxVersion
            FROM
                    product
            GROUP BY productId

   ) vTbl ON
                product.productId= vTbl.productId AND
                product.version= vTbl.MaxVersion

Answer 2

This is just to post the query and show the problem (Micheal answered this one correctly): the

AS  VTBL

needs to be simply

VTBL 

as such

WITH product AS(
 SELECT 1 productId, 2 version FROM DUAL
 union
 SELECT 2 productId, 2 version FROM DUAL
 UNION
 SELECT 3 productId, 3 version FROM DUAL
 union
 SELECT 2 productId, 6 version FROM DUAL
 UNION
 SELECT 3 productId, 4 version FROM DUAL
 UNION
 SELECT 4 productId, 5 version FROM DUAL 
)
SELECT
       PRODUCT.PRODUCTID, 
   product.version
    FROM
            PRODUCT 
    INNER JOIN
            (
                    SELECT
                            productId,
                            MAX(version) AS MaxVersion
                    FROM
                            product
                    GROUP BY productId

           ) /*as*/  VTBL ON
                        product.productId= vTbl.productId AND
                        product.version= vTbl.MaxVersion;

I tend to like being explicit so I would leave the INNER JOIN in, but Michael's response is correct (for he removed the AS)

the AS throws this error

ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

(tested in 11g r1)

Answer 3

The other answers are correct, but if you want to take advantage of Oracle you may want to use analytic functions. This method requires only one table scan instead of two:

select productId, maxVersion
from
(
  select product.productId, version
    ,max(version) over (partition by productId) maxVersion
  from product
)
where version = maxVersion;