LIMIT position for a query in MySQL

Question

I am trying to run this SQL query

SELECT activity_log.* 
from activity_log 
where activity_log.id in (
    select a2.id 
    from activity_log as a2 
    where a2.batch_uuid = activity_log.batch_uuid 
    order by a2.id desc limit 1
    );

I get the error

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I am not sure if an INNER JOIN or another solution is the most effective.

Any help?

Answer 1

MySQL 8.0 solution:

SELECT a.*
FROM (
  SELECT *, ROW_NUMBER_() OVER (PARTITION BY batch_uuid ORDER BY id DESC) AS rownum
  FROM activity_log
) AS a
WHERE a.rownum = 1;

MySQL 5.x solution:

SELECT a1.*
FROM activity_log AS a1
LEFT OUTER JOIN activity_log AS a2
  ON a1.batch_uuid = a2.batch_uuid AND a1.id < a2.id
WHERE a2.id IS NULL;