sql-serversql-server-cesubquery

How to subselect in where statement?

I have the following select statement to get the last login from the user table. This works very well under SQLite, now I'm 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, what's wrong? The subselect? Or something about the comparing of datetime?

Solution

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

    SELECT top 1 LOGIN FROM USERS 
order by LASTLOGIN desc