LIMIT position for a query in MySQL-CodePudding

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?

CodePudding user response：

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;

CodePudding user response：

"id desc limit 1" would be the same as max(id):

SELECT activity_log.* 
from activity_log 
where activity_log.id in (
    select max(a2.id)
    from activity_log as a2 
    where a2.batch_uuid = activity_log.batch_uuid 
);

CodePudding user response：

why not:

select *
from activity_log 
order by id desc limit 1

Since your subquery is just the same table.