This is one of the many things we cannot do in SQL Server CE. Nested SELECTs are legal in SQL Server (including Express), but not in SSCE.

If you aren't using SqlServer Compact Edition you can use TOP to limit the results returned:

SELECT TOP(10) * from ProductTable WHERE ProdID > xxxMaxLastProdID ORDER BY ProdID ASC

This returns...

From "SQL Server 2005 Compact Edition Books Online"

"Queries that can typically be run on SQL Server Compact Edition can also run on Microsoft SQL Server; however, many of the features...

Stored procedures are not available for SQL Server CE.

But you already knew that.