CODEWITHSUNDEEP
sql-serversql-server-cesubquery
Christopher Klewes
Christopher Klewes

Reputation: 11445

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?

Upvotes: 0

Views: 420

Answers (2)

Raj More
Raj More

Reputation: 48024

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

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

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

SELECT top 1 LOGIN FROM USERS 
order by LASTLOGIN desc

Upvotes: 3

Related Questions