How to write this query?

Question

I have a table containing users... one way or another some users were doubled up (have multiple records). I want to pull every user record distinctly by email address and for the users who have multiple records I want to pull only the record that has the most logins.

--USERS----------------------------
ID EMAIL           TOTAL_LOGINS
1  [email protected]   3
2  [email protected]   1
3  [email protected]  1
4  [email protected]  45
5  [email protected]  6
6  [email protected]   2

What I would like the query to return is this:

ID EMAIL           TOTAL_LOGINS
1  [email protected]   3
4  [email protected]  45
5  [email protected]  6
6  [email protected]   2

Can someone help me with this query?

Thanks!

Answer 1

SELECT    EMAIL, MAX(TOTAL_LOGINS)
FROM      USERS
GROUP BY  EMAIL

EDIT:

This will do what you want in MS SQL.

SELECT    * -- Bad practice. I'm just showing that you can select anything.
FROM      USERS
WHERE     ID = (SELECT    TOP 1 ID
                FROM      USERS u
                WHERE     u.EMAIL = USERS.EMAIL
                ORDER BY  TOTAL_LOGINS DESC)