Emulating row_number in MySQL 5.7 with date condition

I am working in MySQL 5.7.34 and I have the following:

create table employees (
    employee_id int not null auto_increment,
    first_name varchar(100) not null,
    last_name varchar(100) not null,
    primary key (employee_id)
    );
        
create table documents (
    document_id int not null auto_increment,
    title varchar(100) not null,
    last_modified datetime not null,
    employee_id int not null,
    primary key (document_id),
    foreign key (employee_id) references employees(employee_id)
);

I have created a db-fiddle here. The columns shown in the fiddle are only a few, but in the real data set, the documents table will have 20 or more columns and all need to show.

SQL Query:

-- simply query
select e.employee_id,
       e.first_name,
       e.last_name,
       d.title
from employees e
inner join documents d on e.employee_id = d.employee_id;

Current Output:

employee_id first_name last_name title
1 John Doe JD_Doc_Updated
1 John Doe JD_Doc
2 Mike Anderson MA_Doc

Desired Output:

For each employee, I want to grab the most recent document only. In MS SQL Server (and recent versions of MySQL), I could use something like ROW_NUMBER() OVER(PARTITION BY d.employee_id ORDER BY d.last_modified DESC) AS num

employee_id first_name last_name title
1 John Doe JD_Doc_Updated
2 Mike Anderson MA_Doc

I am not sure how to achieve the same here.

I had a read of this question, and I don’t think the problem here is the same, that other question deals with data on the same table and isn’t dealing with datetime as the data type. Unless I’m missing something?

Answer

A pretty simple method uses a correlated subquery:

select e.employee_id, e.first_name, e.last_name,
       d.title
from employees e join
     documents d
     on e.employee_id = d.employee_id
where d.last_modified = (select max(d2.last_modified)
                         from documents d2
                         where d2.employee_id = d.employee_id
                        );

For performance, you want an index on documents(employee_id, last_modified).

Here is a db-fiddle.