MySQL - Select query to return all cols but only 1 of each combination of 2 identifying columns

Question

I need to write a mysql query that returns all columns, but only 1 of each set of identifying columns. i.e.

I have a table with a primary key on 3 fields (firstName, lastName, effDate). I can have x amount of rows with the same firstName and lastName but all have different effDates. I've included an example set of data below. I need to write a query that returns the most recent firstName + lastName combination. Is this possible? something like:

SELECT DISTINCT(firstName, lastName), * FROM table ORDER BY effData DESC;

example data:

FirstName lastName effDate

Will Johnson 2021年02月02日

Will Johnson 2021年03月02日

Will Johnson 2021年04月02日

Sam Red 2021年02月23日

Sam Red 2021年04月28日

example return:

FirstName lastName effDate

Will Johnson 2021年04月02日

Sam Red 2021年04月28日

• Rick James: Which effDate do you want to show?

Answer 1

To get from your example data to the desired result, all you need to do is group by firstName, lastName and take the max effDate:

select firstName
 ,lastName
 ,max(effDate) effDate
from table
group by firstName
 ,lastName

If you want to grab more information from the rows that contain the most recent effDate you can use an analytic function to provide an ordered column that can be used for filtering:

select firstName
 ,lastName
 ,effDate
from (
select firstName
 ,lastName
 ,effDate
 ,row_number() over (partition by firstName, lastName order by effDate desc) rn
from table
 ) sq
where rn = 1

(assuming effDate can't be null)

Answer 2

You can't use DISTINCT(firstName, lastName) with * due to ambiguity. To get the most recent row for each firstName, lastName pair based on effDate, use ROW_NUMBER() (MySQL 8.0+):

SELECT firstName, lastName, effDate
FROM (
 SELECT firstName, lastName, effDate,
 ROW_NUMBER() OVER (PARTITION BY firstName, lastName ORDER BY effDate DESC) AS rn
 FROM table_name
) t
WHERE rn = 1;

For MySQL versions before 8.0, use a correlated subquery:

SELECT firstName, lastName, effDate
FROM table_name t1
WHERE effDate = (
 SELECT MAX(effDate)
 FROM table_name t2
 WHERE t2.firstName = t1.firstName AND t2.lastName = t1.lastName
);

Both queries return the most recent row per firstName, lastName pair, matching your example output.