Sql – How to subselect in where statement in SQL Server CE

sql-serversql-server-cesql-subselect

i have the following select statement to get the last login from the user table. this works very well under sqlite, now im porting the database and have Compact Edition from Microsoft.

SELECT LOGIN 
  FROM USERS 
 WHERE LASTLOGIN = (SELECT MAX(LASTLOGIN) FROM USERS)

The lastlogin column is datetime.

This doesn't seems to work, whats wrong? the subselect? or something about the comparing of datetime? can you help me how to do it right?

chrsk

Best Solution

this makes only one table lookup and not 2 from your previous statement

SELECT top 1 LOGIN FROM USERS 
order by LASTLOGIN desc