Fetching last 2 rows of multiple user in MySQL

Question

I have a table to list the transaction made by users.

mysql> select * from transaction;
+-----------+----------+------------+
| emp_id_fk | trans_id | trans_date |
+-----------+----------+------------+
|         1 |        1 | 2008-01-01 |
|         1 |        2 | 2019-01-01 |
|         1 |        3 | 2020-01-01 |
|         2 |        4 | 2020-01-10 |
|         2 |        5 | 2020-01-16 |
|         2 |        6 | 2020-01-15 |
+-----------+----------+------------+
6 rows in set (0.00 sec)

I want to know the last 2 transactions made by the users along with their transaction ID. The output should look something like this.

+-----------+----------+------------+
| emp_id_fk | trans_id | trans_date |
+-----------+----------+------------+
|         1 |        2 | 2019-01-01 |
|         1 |        3 | 2020-01-01 |
|         2 |        5 | 2020-01-16 |
|         2 |        6 | 2020-01-15 |
+-----------+----------+------------+

I've tried inner joins and group by clause but of no use. How can I generate this output?

Answer 1

If you are running MySQL 8.0, you can use window fuctions:

select *
from (
    select t.*, row_number() over(partition by emp_id_fk order by trans_date desc) rn
    from transactions t
) t
where rn <= 2
order by emp_id_fk, trans_date

If there may be more than one transaction for a given customer on the same date, consider adding another sorting criteria to the order by clause of the window function, such as trans_id for example:

row_number() over(partition by emp_id_fk order by trans_date desc, , trans_id) rn desc

In older versions, you could use a correlated subquery:

select t.*
from transactionts t
where (
    select count(*)
    from transactions t1
    where t1.trans_date >= t.trans_date and t1.emp_id_fk = t.emp_id_fk
) <= 2