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

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)