Emulating row_number in MySQL 5.7 with date condition

Question

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?

Does this answer your question? [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) — Serg, Jul 14 '21 at 09:21
Do you want only one column from the joined table? That would simplify things a bit. — Salman A, Jul 14 '21 at 10:08
Does this answer your question? [Get top n records for each group of grouped results](https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — astentx, Jul 16 '21 at 11:25

score 1 · Answer 1 · answered Jul 14 '21 at 09:56

You are using an old MySQL version which doesn't feature window functions. So, to get the latest document per employee you need two steps:

get the maximum document ID or date
get the row belonging to that ID or date

Let's say "latest document" refers to the one that was last modified:

select *
from employees e
join documents d 
  on d.employee_id = e.employee_id
  and (d.employee_id, d.last_modified) in
  (
    select employee_id, max(last_modified)
    from documents
    group by employee_id
  )
order by e.employee_id;

The IN clause ensures that the joined document is in the set of latest documents. There are several other ways to write this. You can replace the IN clause with a correlated clause to refer to the employee's ID. Or you could join the tables and have a criteria that NOT EXISTS a newer document. Or you could upgrade to MySQL 8 :-)

score 1 · Accepted Answer · answered Jul 14 '21 at 10:22

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.

Meysam Asadi · Answer 3 · 2021-07-14T10:19:12.777

You can use the method. The first method is the same as row-number in MSSQL

select employee_id,first_name,last_name,title
from
(select *,
    @row_number:=CASE
        WHEN @empl_no = employee_id 
          THEN 
              @row_number + 1
          ELSE 
               1
        END AS num,
    @empl_no := employee_id EmplNumber
from 
   (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
   order by d.last_modified desc) t1,(SELECT @empl_no :=0,@row_number:=0) as t2) T
where num = 1
order by employee_id

or you can use group by then select max last_modified. finally join the documents and fetch title column.

 select t1.employee_id,t1.first_name,t1.last_name,title,t2.title
        from
           (select e.employee_id,
               
               max(e.first_name) as first_name,
               max(e.last_name) as last_name,
               max(d.last_modified ) as last_modified
           from employees e
           inner join documents d on e.employee_id = d.employee_id
           group by e.employee_id) t1
           
           join documents t2 on t1.last_modified = t2.last_modified

Emulating row_number in MySQL 5.7 with date condition

3 Answers3