CODEWITHSUNDEEP
mysqlgroup-by
fadedbee
fadedbee

Reputation: 44739

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

Is there a more efficient way of doing the following?

select * 
    from foo as a
    where a.id = (select max(id) from foo where uid = a.uid group by uid)
    group by uid;
)

This answer looks similar, but is this answer the best way of doing this - How to select the first row for each group in MySQL?

Thanks,

Chris.

P.S. the table looks like:

CREATE TABLE foo (
    id INT(10) NOT NULL AUTO_INCREMENT,
    uid INT(10) NOT NULL,
    value VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `uid` (`uid`)
)

data:

id, uid, value
 1,   1, hello
 2,   2, cheese
 3,   2, pickle
 4,   1, world

results:

id, uid, value
 3,   2, pickle
 4,   1, world

See http://www.barricane.com/2012/02/08/mysql-select-last-matching-row.html for more details.

Upvotes: 17

Views: 72110

Answers (6)

Tahajuda Mandariansah
Tahajuda Mandariansah

Reputation: 9

This code works on me:

SELECT * FROM foo GROUP BY foo.uid 
HAVING MAX(foo.id)

Upvotes: -2

Swapnil Kumbhar
Swapnil Kumbhar

Reputation: 460

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

Upvotes: 10

Vahid Najafi
Vahid Najafi

Reputation: 5233

The easiest way : you can select from selected list that is sorted.

SELECT * FROM (SELECT * FROM foo order by id DESC) AS footbl
group by uid
order by id DESC

Upvotes: 1

Peter Gill
Peter Gill

Reputation: 21

This has worked for me thanks! 

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;

my version:

SELECT * FROM messages t1 JOIN (SELECT MAX(id) id FROM messages  where uid = 8279 and actv=1 GROUP BY uid ) t2 ON t1.id = t2.id ORDER BY datex desc LIMIT 0,10;

this code below doesn't return all rows I want because if max id column is an inactive one then it skips the group even if the group has active rows..

select * from messages where uid = 8279 and actv=1 and id In (Select max(id) From messages Group By uid) order by datex desc;

Upvotes: 2

Devart
Devart

Reputation: 121902

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;

Upvotes: 38

user319198
user319198

Reputation:

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;

Upvotes: 3

Related Questions