CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
omnomah
omnomah

Reputation: 95

SQL Select unique values by one column with the latest value by another column

I have the following table structure:

leads:

name  | email | date
-------------------------------------------------------
John  | [email protected]  | 2020-01-21
J     | [email protected]  | 2020-01-20
Alex  | [email protected]  | 2020-01-19
A     | [email protected]  | 2020-01-18
James | [email protected] | 2020-01-17

I need to select only rows with UNIQUE emails and the LATEST associated name so the expected result is:

name  | email | date
-------------------------------------------------------
John  | [email protected]  | 2020-01-21
Alex  | [email protected]  | 2020-01-19
James | [email protected] | 2020-01-17

Upvotes: 0

Views: 200

Answers (1)

zip
zip

Reputation: 4061

Use not exists

select * from tbl a 
where not exist(select 1 from tbl b where a.email = b.email  and a.date < b.date)

Upvotes: 3

Related Questions