Returning the 'last' row of each 'group by' in MySQL

Try this query -

SELECT t1.* FROM foo t1
  JOIN (SELECT uid, MAX(id) id FROM foo GROUP BY uid) t2
    ON t1.id = t2.id AND t1.uid = t2.uid;

Then use EXPLAIN to analyze queries.

SELECT t1.* FROM foo t1
  LEFT JOIN foo t2
    ON t1.id < t2.id AND t1.uid = t2.uid
WHERE t2.id is NULL;

Returning the last row of each GROUP BY in MySQL with WHERE clause:

SELECT *
FROM foo
WHERE id IN (
  SELECT Max(id)
  FROM foo
  WHERE value='XYZ'
  GROUP BY u_id
)
LIMIT 0,30

if table is big in size. Make view containing all last row id

create view lastrecords as (select max(id) from foo where uid = a.uid group by uid)

Now join your main query with this view. It will be faster.

  SELECT t1.* FROM tablename as t1
    JOIN lastrecords as  t2
    ON t1.id = t2.id AND t1.uid = t2.uid;

OR You can do join with last records direct in query also:

SELECT t1.* FROM tablename as t1
JOIN (SELECT uid, MAX(id) id FROM tablename GROUP BY id) as  t2
ON t1.id = t2.id AND t1.uid = t2.uid;

Related