17

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.

Community
  • 1
  • 1
fadedbee
  • 42,671
  • 44
  • 178
  • 308
  • This is a correlated subquery. Generally, an uncorrelated subquery will prove faster, as per Devart's example. – Strawberry Apr 18 '13 at 09:06
  • possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) – Alastair Irvine Apr 13 '15 at 14:32

6 Answers6

38

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;
Devart
  • 119,203
  • 23
  • 166
  • 186
  • This doesn't work as a view - I get "ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause", whereas my code does. – fadedbee Feb 08 '12 at 14:18
  • 1
    I didn't know that you was going to use it for view. ...I have added another one. – Devart Feb 08 '12 at 14:36
  • Thanks, I'll have to do some tests on all three alternatives and report back. (I managed to make your first solution work for views by making the subquery a separate view.) – fadedbee Feb 09 '12 at 11:06
  • 2
    Note, sometimes views are slower than simple queries - http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker – Devart Feb 10 '12 at 06:35
10

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
josemmo
  • 6,523
  • 3
  • 35
  • 49
Swapnil Kumbhar
  • 440
  • 4
  • 10
3

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;
2

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;
kleopatra
  • 51,061
  • 28
  • 99
  • 211
Peter Gill
  • 21
  • 2
1

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
Vahid Najafi
  • 4,654
  • 11
  • 43
  • 88
-2

This code works on me:

SELECT * FROM foo GROUP BY foo.uid 
HAVING MAX(foo.id)
סטנלי גרונן
  • 2,917
  • 23
  • 46
  • 68
Tahajuda Mandariansah
  • 9
  • 4