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.
-- 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;
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
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?
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
Here is a db-fiddle.