CODEWITHSUNDEEP
mysql
David
David

Reputation: 36354

Selecting newest distinct email address from table

Very quick one:

I've done this before, but having a bit of a mind blank right now.

SELECT max(id) as id, username, email 
FROM user_info 
WHERE username!='' 
GROUP by id,email

Unfortunately this is not working. It will select the max id but not then the corresponding data from that row.

Upvotes: 0

Views: 340

Answers (2)

rizon
rizon

Reputation: 8187

Select DISTINCT(email), MAX(id)
FROM user_info
GROUP by email
ORDER BY id DESC

Upvotes: 0

Taryn
Taryn

Reputation: 247680

If you have multiple ids per email address, then you can use max(id) and group by the email only.

SELECT max(id) as id, username, email 
FROM user_info 
WHERE username <> '' 
GROUP by email, username

You can use a subquery which will return the max id in the subquery and then you use that to self join on the table to return the most recent id, username and email:

SELECT u.id, u.username, u.email 
FROM user_info u
INNER JOIN
(
  select max(id) maxid, email
  from user_info
  where username <> '' 
  group by email
) u2
  on u.id = u2.maxid

See SQL Fiddle with Demo

Upvotes: 1

Related Questions