Question 113408 in Mysql Question posted in
The official MySQL documentation can be found here.

LIMIT position for a query in MySQL

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?

Tags:

3

Answers


  1. 0

    "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 
);
    Login or Signup to reply.
  2. 0

    why not:

    select *
from activity_log 
order by id desc limit 1

    Since your subquery is just the same table.

    Login or Signup to reply.
  3. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search