Your query makes no sense at all - even ignoring the sytax errors. What it effectivly comes down to is:
SELECT MAX(column) AS largest FROM table WHERE column > largest
Which is clearly silly - it can't return any records that are larger than the largest value, because if a record was larger than the largest, it would be the largest! Paradox, I'm afraid.
If you want to return the largest value in a column then just do that:
SELECT MAX(Product_Key) FROM Product_Dimension
That will return a single value, the largest in the table.