How to subselect in where statement in SQL Server CE?

Question

Hi,

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

Answer 1

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

SELECT top 1 LOGIN FROM USERS 
order by LASTLOGIN desc

Answer 2

This will give you the latest login for the user of your choice

SELECT top 1 Login
FROM Users
WHERE USERS.LOGIN = @YourUser
ORDER BY LastLogin desc