By Vijay Dev

2009-08-21 17:04:04 8 Comments

There is a table messages that contains data as shown below:

Id   Name   Other_Columns
1    A       A_data_1
2    A       A_data_2
3    A       A_data_3
4    B       B_data_1
5    B       B_data_2
6    C       C_data_1

If I run a query select * from messages group by name, I will get the result as:

1    A       A_data_1
4    B       B_data_1
6    C       C_data_1

What query will return the following result?

3    A       A_data_3
5    B       B_data_2
6    C       C_data_1

That is, the last record in each group should be returned.

At present, this is the query that I use:

FROM messages

But this looks highly inefficient. Any other ways to achieve the same result?


@Bill Karwin 2009-08-21 17:39:46

MySQL 8.0 now supports windowing functions, like almost all popular SQL implementations. With this standard syntax, we can write greatest-n-per-group queries:

WITH ranked_messages AS (
  FROM messages AS m
SELECT * FROM ranked_messages WHERE rn = 1;

Below is the original answer I wrote for this question in 2009:

I write the solution this way:

FROM messages m1 LEFT JOIN messages m2
 ON ( = AND <

Regarding performance, one solution or the other can be better, depending on the nature of your data. So you should test both queries and use the one that is better at performance given your database.

For example, I have a copy of the StackOverflow August data dump. I'll use that for benchmarking. There are 1,114,357 rows in the Posts table. This is running on MySQL 5.0.75 on my Macbook Pro 2.40GHz.

I'll write a query to find the most recent post for a given user ID (mine).

First using the technique shown by @Eric with the GROUP BY in a subquery:

SELECT p1.postid
FROM Posts p1
INNER JOIN (SELECT pi.owneruserid, MAX(pi.postid) AS maxpostid
            FROM Posts pi GROUP BY pi.owneruserid) p2
  ON (p1.postid = p2.maxpostid)
WHERE p1.owneruserid = 20860;

1 row in set (1 min 17.89 sec)

Even the EXPLAIN analysis takes over 16 seconds:

| id | select_type | table      | type   | possible_keys              | key         | key_len | ref          | rows    | Extra       |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                       | NULL        | NULL    | NULL         |   76756 |             | 
|  1 | PRIMARY     | p1         | eq_ref | PRIMARY,PostId,OwnerUserId | PRIMARY     | 8       | p2.maxpostid |       1 | Using where | 
|  2 | DERIVED     | pi         | index  | NULL                       | OwnerUserId | 8       | NULL         | 1151268 | Using index | 
3 rows in set (16.09 sec)

Now produce the same query result using my technique with LEFT JOIN:

SELECT p1.postid
FROM Posts p1 LEFT JOIN posts p2
  ON (p1.owneruserid = p2.owneruserid AND p1.postid < p2.postid)
WHERE p2.postid IS NULL AND p1.owneruserid = 20860;

1 row in set (0.28 sec)

The EXPLAIN analysis shows that both tables are able to use their indexes:

| id | select_type | table | type | possible_keys              | key         | key_len | ref   | rows | Extra                                |
|  1 | SIMPLE      | p1    | ref  | OwnerUserId                | OwnerUserId | 8       | const | 1384 | Using index                          | 
|  1 | SIMPLE      | p2    | ref  | PRIMARY,PostId,OwnerUserId | OwnerUserId | 8       | const | 1384 | Using where; Using index; Not exists | 
2 rows in set (0.00 sec)

Here's the DDL for my Posts table:

CREATE TABLE `posts` (
  `PostId` bigint(20) unsigned NOT NULL auto_increment,
  `PostTypeId` bigint(20) unsigned NOT NULL,
  `AcceptedAnswerId` bigint(20) unsigned default NULL,
  `ParentId` bigint(20) unsigned default NULL,
  `CreationDate` datetime NOT NULL,
  `Score` int(11) NOT NULL default '0',
  `ViewCount` int(11) NOT NULL default '0',
  `Body` text NOT NULL,
  `OwnerUserId` bigint(20) unsigned NOT NULL,
  `OwnerDisplayName` varchar(40) default NULL,
  `LastEditorUserId` bigint(20) unsigned default NULL,
  `LastEditDate` datetime default NULL,
  `LastActivityDate` datetime default NULL,
  `Title` varchar(250) NOT NULL default '',
  `Tags` varchar(150) NOT NULL default '',
  `AnswerCount` int(11) NOT NULL default '0',
  `CommentCount` int(11) NOT NULL default '0',
  `FavoriteCount` int(11) NOT NULL default '0',
  `ClosedDate` datetime default NULL,
  PRIMARY KEY  (`PostId`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `PostTypeId` (`PostTypeId`),
  KEY `AcceptedAnswerId` (`AcceptedAnswerId`),
  KEY `OwnerUserId` (`OwnerUserId`),
  KEY `LastEditorUserId` (`LastEditorUserId`),
  KEY `ParentId` (`ParentId`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`PostTypeId`) REFERENCES `posttypes` (`PostTypeId`)

@Eric 2009-08-21 18:19:39

Really? What happens if you have a ton of entries? For example, if you're working w/ an in-house version control, say, and you have a ton of versions per file, that join result would be massive. Have you ever benchmarked the subquery method with this one? I'm pretty curious to know which would win, but not curious enough to not ask you first.

@Vijay Dev 2009-08-21 18:30:17

Thanks Bill. That works perfectly. Can you provide more information regarding the performance of this query against the join provided by Eric?

@Eric 2009-08-21 18:44:43

Did some testing. On a small table (~300k records, ~190k groups, so not massive groups or anything), the queries tied (8 seconds each).

@Eric 2009-08-21 18:45:13

I should note that's with a composite key and no indexing. It was a throw-away staging table :)

@Eric 2009-08-21 20:10:44

Wow, great info. I ran my test against SQL Server 2008, so it's intriguing to see how MySQL differs with these queries. Again showing you that explain is your friend!

@Bill Karwin 2009-08-21 20:15:25

Aha! I was wondering how you got such different results from mine. In many cases of using GROUP BY, MySQL creates a temporary table on disk, leading to expensive I/O. Best to avoid GROUP BY if you can in MySQL. And yes, always analyze queries with EXPLAIN when performance is important.

@Eric 2009-08-21 20:28:08

@Bill: SQL Server hates or, MySQL hates group by. One of these days we'll get an RDBMS that likes all of SQL. Thought looking at the explain, it looks like if you put the where clause inside the subquery, also, it would return a much smaller rowset.

@Bill Karwin 2009-08-21 21:55:53

@Eric: regarding putting a WHERE restriction inside the subquery, yes, but then you don't need the GROUP BY either.

@Eric 2009-08-22 03:40:33

@Bill: Ah, I always forget that MySQL will let you drop GROUP BY. Of course, dropping it would be the most efficient way to run that query for a specific user. SQL Server is less forgiving in with its GROUP BY. If it's in the select, it has to be in the GROUP BY. Of course, it can be accomplished with the over clause, which is just magical, really.

@JYelton 2012-02-20 21:52:24

As @newt indicates, this query was slow for me (10+ minutes on SQL Server 2008) with large datasets. I need to select the last data per group from a 3.5 million row table.

@Bill Karwin 2012-02-20 22:35:48

@JYelton, with SQL Server 2008 you should use a CTE with windowing functions.

@Robert Harvey 2012-02-21 18:06:50

@BillKarwin: See, especially the comments below Adam Rackis' answer. Let me know if you want to reclaim your answer on the new question.

@Bill Karwin 2012-02-21 18:46:44

@RobertHarvey, thanks, I will follow up on the Meta post you linked to.

@webenformasyon 2014-07-05 14:55:09

SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON ( = AND < WHERE IS NULL and m1.anotherID; fails if you have only one record at anotherID

@Bill Karwin 2014-07-05 16:31:10

@webenformasyon, the way you've written that condition, the query would fail if m1.anotherID is zero. You have no comparison term, you have only treated anotherID as if it is a boolean.

@Bill Karwin 2014-07-16 21:18:28

I just got a downvote. Downvoter, can you please explain why you object to this answer? Perhaps I can improve it.

@Hybris95 2014-12-08 15:09:30

Just wanted to mention that this solution works on Derby databases also.

@Tim 2015-07-02 13:49:40

@BillKarwin this does not work with non-unique id's sice < comparison - is it possible to use it somehow with <= so it works when you have duplicate id's ?

@Bill Karwin 2015-07-03 07:13:14

@Tim, no, <= will not help if you have a non-unique column. You must use a unique column as a tiebreaker.

@Salman A 2015-10-16 12:12:13

The performance degrades exponentially as the number of rows increases or when groups become larger. For example a group consisting of 5 dates will yield 4+3+2+1+1 = 11 rows via left join out of which one row is filtered in the end. Performance of joining with grouped results is almost linear. Your tests look flawed.

@Bill Karwin 2015-10-16 15:40:53

@SalmanA nevertheless, I did run these tests and got the results I show. If you want to do your own test, and post your own answer showing the results, be my guest.

@ahmed 2016-11-02 13:39:20

@BillKarwin what if I've non-unique column, what can be done to get rid of duplicates?

@Bill Karwin 2016-11-02 13:41:52

@ahmed, see "solution 2" in newtover's answer.

@ahmed 2016-11-03 07:53:29

@BillKarwin my workaround is that I added an extra or condition in the join and where clauses in your proposed query: SELECT m1.* FROM messages m1 LEFT JOIN messages m2 ON ( = AND ( < OR m1.non-unique-column < m2.non-unique-column)) WHERE IS NULL AND m2.non-unique-column IS NULL; it did the trick, although it's not really that optimized, but I'm using limit for pagination and it's fast enough for my case. Thank you

@Wakan Tanka 2016-12-15 13:27:48

@BillKarwin where did you get the copy of StackOverflow database?

@Bill Karwin 2016-12-15 18:31:46

@R.W 2017-04-29 19:54:10

@BillKarwin thanks for the query. I am quite new to SQL/Joins and wondering how to modify the same query to do something similar like 1) Get the first record instead of the last, and 2) Only get records for a certain date ( My Table has a date field ) . Thanks once again

@Bill Karwin 2017-04-29 21:41:03

@MenonM, You should be able to do that yourself given what I have shown above.

@aexl 2017-05-18 19:40:46

This is a godsend. Your query does the job and is lightning fast. I needed to grab the latest login time of each user and it worked. Thanks a lot!

@Cruncher 2017-11-16 11:58:12

If the explain takes almost as long as the query itself, does that mean if this was a prepared query it would run much quicker, because it seems most of the time is spent deciding how to retrieve the data, than it is retrieving the data.

@Bill Karwin 2017-11-16 15:49:04

@Cruncher, Not necessarily. It takes a long time to do the EXPLAIN because the optimizer actually executes the subquery for the derived table and creates a temp table for it, before it can estimate the optimization plan.

@Shahid Karimi 2018-03-29 19:42:54

After 5 days googling your answer is the accurate one. All others were making shit

@Yoseph 2018-04-30 02:33:48

@BillKarwin (part 1 of 3) Using my test data (~1.5 million rows with 100 groups) testing on 8.0.4-rc it shows that your queries can be very inefficient:

@Yoseph 2018-04-30 02:35:46

@BillKarwin (part 2 of 3) Firstly your messages query using the LEFT JOIN method takes too long that I had to kill it. Your PARTITION query takes well over 2 seconds. My LIMIT 1 query takes a fraction of a second (although still inefficient I have not seen a more efficient way yet): SELECT,, m1.other_columns FROM (SELECT DISTINCT FROM messages m2) g INNER JOIN messages m1 ON = ( SELECT FROM messages m3 WHERE = ORDER BY DESC LIMIT 1 );

@Yoseph 2018-04-30 02:36:07

@BillKarwin (part 3 of 3) Likewise for your posts query, it is taking more than 20 seconds using my test data. However My LIMIT 1 query takes 0 seconds: SELECT p.postID FROM posts p WHERE p.ownerUserID = 20860 ORDER BY p.postID DESC LIMIT 1; I would be interested to know how long my LIMIT 1 queries take using your test data?

@Bill Karwin 2018-04-30 14:49:41

@Yoseph, it should not surprise you that I don't still have the test data that I used for this answer in 2009. Anyway, good for you for doing testing. Depending on the dataset and the version of MySQL, a different solution may be better. Only by testing can we be sure which one works best for any specific case.

@Houman 2018-05-28 16:33:37

@BillKarwin Even your latest update isn't working on MariaDB 10.0.34.

@Bill Karwin 2018-05-28 18:01:53

@Houman, I don't use or support MariaDB. But according to their documentation on WITH syntax, they don't support it until MariaDB 10.2.1.

@Yoseph 2018-06-04 01:15:39

@BillKarwin Any chance that you tested your MySQL 8 answer (MySQL 8 was only recently released)? Using my test data that is also proving to be inefficient, see… (if you did test that answer then I would be very interested to know how my LIMIT 1 way performs with your test data)

@Bill Karwin 2018-06-04 06:22:58

@Yoseph, You already have my answer on this subject. Different versions of MySQL have different behavior. Differences in data will also lead to different behavior. You tested and found a query solution that performs well for your dataset. Call it a win and move on.

@Mon 2019-05-23 07:21:03

Hi. Sorry for newbie question, but can you please explain how = AND < works in getting only the latest record? and what the purpose of WHERE IS NULL is? Does it happen in steps? Thank you! :)

@Bill Karwin 2019-05-23 17:17:15

@Mon I wrote a similar answer with more explanation of how the outer join works here:…

@lipinf 2019-07-17 02:00:07

@BillKarwin how can I do if have an extra column that store an Int and I want to retrieve only last record in each group that match with an specific value of this extra column?

@Bill Karwin 2019-07-17 02:07:10

@lipinf Use a condition in your WHERE clause for that int column, and then also include that column in the JOIN condition.

@vee 2020-01-30 09:01:24

WITH and ROW_NUMBER() are supported in MariaDB 10.2.1 (ROW_NUMBER() support since 10.2)

@Vipin 2013-12-25 08:36:42

Solution by sub query fiddle Link

select * from messages where id in
(select max(id) from messages group by Name)

Solution By join condition fiddle link

select m1.* from messages m1 
left outer join messages m2 
on (< and )
where is null

Reason for this post is to give fiddle link only. Same SQL is already provided in other answers.

@Vipin 2018-07-04 17:21:14

@AlexanderSuraphel mysql5.5 is not available in fiddle now, fiddle link was created using that. Now a days fiddle supports mysql5.6, i changed database to mysql 5.6 and i am able to build schema and run the sql.

@Ka. 2019-12-10 08:54:48

Another approach :

Find the propertie with the max m2_price withing each program (n properties in 1 program) :

select * from properties p
join (
    select max(m2_price) as max_price 
    from properties 
    group by program_id
) p2 on (p.program_id = p2.program_id)
having p.m2_price = max_price

@newtover 2012-01-06 11:21:09

UPD: 2017-03-31, the version 5.7.5 of MySQL made the ONLY_FULL_GROUP_BY switch enabled by default (hence, non-deterministic GROUP BY queries became disabled). Moreover, they updated the GROUP BY implementation and the solution might not work as expected anymore even with the disabled switch. One needs to check.

Bill Karwin's solution above works fine when item count within groups is rather small, but the performance of the query becomes bad when the groups are rather large, since the solution requires about n*n/2 + n/2 of only IS NULL comparisons.

I made my tests on a InnoDB table of 18684446 rows with 1182 groups. The table contains testresults for functional tests and has the (test_id, request_id) as the primary key. Thus, test_id is a group and I was searching for the last request_id for each test_id.

Bill's solution has already been running for several hours on my dell e4310 and I do not know when it is going to finish even though it operates on a coverage index (hence using index in EXPLAIN).

I have a couple of other solutions that are based on the same ideas:

  • if the underlying index is BTREE index (which is usually the case), the largest (group_id, item_value) pair is the last value within each group_id, that is the first for each group_id if we walk through the index in descending order;
  • if we read the values which are covered by an index, the values are read in the order of the index;
  • each index implicitly contains primary key columns appended to that (that is the primary key is in the coverage index). In solutions below I operate directly on the primary key, in you case, you will just need to add primary key columns in the result.
  • in many cases it is much cheaper to collect the required row ids in the required order in a subquery and join the result of the subquery on the id. Since for each row in the subquery result MySQL will need a single fetch based on primary key, the subquery will be put first in the join and the rows will be output in the order of the ids in the subquery (if we omit explicit ORDER BY for the join)

3 ways MySQL uses indexes is a great article to understand some details.

Solution 1

This one is incredibly fast, it takes about 0,8 secs on my 18M+ rows:

SELECT test_id, MAX(request_id) AS request_id
FROM testresults
GROUP BY test_id DESC;

If you want to change the order to ASC, put it in a subquery, return the ids only and use that as the subquery to join to the rest of the columns:

SELECT test_id, request_id
    SELECT test_id, MAX(request_id) AS request_id
    FROM testresults
    GROUP BY test_id DESC) as ids
ORDER BY test_id;

This one takes about 1,2 secs on my data.

Solution 2

Here is another solution that takes about 19 seconds for my table:

SELECT test_id, request_id
FROM testresults, (SELECT @group:=NULL) as init
WHERE IF(IFNULL(@group, -1)[email protected]:=test_id, 0, 1)
ORDER BY test_id DESC, request_id DESC

It returns tests in descending order as well. It is much slower since it does a full index scan but it is here to give you an idea how to output N max rows for each group.

The disadvantage of the query is that its result cannot be cached by the query cache.

@Pacerier 2015-02-03 03:44:42

Please link to a dump of your tables so that people can test it on their platforms.

@giò 2017-03-09 09:57:16

Solution 1 can't work, you can't select request_id without having that in group by clause,

@newtover 2017-03-31 14:58:37

@giò, this is answer is 5 years old. Until MySQL 5.7.5 ONLY_FULL_GROUP_BY was disabled by default and this solution worked out of the box…. Now I'm not sure if the solution still works when you disable the mode, because the implementation of the GROUP BY has been changed.

@Jin 2017-05-09 15:45:58

If you wanted ASC in the first solution, would it work if you turn MAX to MIN?

@newtover 2017-05-09 20:10:05

@JinIzzraeel, you have MIN by default at the top of each group (it is the order of the covering index): SELECT test_id, request_id FROM testresults GROUP BY test_id; would return the minimum request_id for each test_id.

@Gerry 2020-05-07 16:23:15

That's cool, but I need all of the data from the row that has the max request_id. If I try to add a column I get this error: Column [colname] is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

@kiruba 2020-01-15 06:07:02

Hope below Oracle query can help:

WITH Temp_table AS
    Select id, name, othercolumns, ROW_NUMBER() over (PARTITION BY name ORDER BY ID 
    desc)as rank from messages
Select id, name,othercolumns from Temp_table where rank=1

@Abhishek Sengupta 2019-10-17 10:18:49


Hi, this query might help :



  `Id` IN (
   `Id` DESC

@ShriP 2019-02-07 23:31:50

SELECT * FROM table_name WHERE primary_key IN (SELECT MAX(primary_key) FROM table_name GROUP BY column_name )

@Yagnesh bhalala 2019-04-27 08:03:10

we will look at how you can use MySQL at getting the last record in a Group By of records. For example if you have this result set of posts.

id category_id post_title

1 1 Title 1

2 1 Title 2

3 1 Title 3

4 2 Title 4

5 2 Title 5

6 3 Title 6

I want to be able to get the last post in each category which are Title 3, Title 5 and Title 6. To get the posts by the category you will use the MySQL Group By keyboard.

select * from posts group by category_id

But the results we get back from this query is.

id category_id post_title

1 1 Title 1

4 2 Title 4

6 3 Title 6

The group by will always return the first record in the group on the result set.

SELECT id, category_id, post_title FROM posts WHERE id IN ( SELECT MAX(id) FROM posts GROUP BY category_id );

This will return the posts with the highest IDs in each group.

id category_id post_title

3 1 Title 3

5 2 Title 5

6 3 Title 6

Reference Click Here

@Amir Fo 2019-04-07 06:26:53

You can group by counting and also get the last item of group like:

    COUNT(user) AS count,
    MAX(id) as last
FROM request 

@michal.jakubeczy 2018-05-02 15:05:59

If performance is really your concern you can introduce a new column on the table called IsLastInGroup of type BIT.

Set it to true on the columns which are last and maintain it with every row insert/update/delete. Writes will be slower, but you'll benefit on reads. It depends on your use case and I recommend it only if you're read-focused.

So your query will look like:

SELECT * FROM Messages WHERE IsLastInGroup = 1

@Lawrence 2019-08-26 19:10:51

Some tables in Moodle has a flag column like this.

@Yoseph 2018-04-18 07:45:23

Clearly there are lots of different ways of getting the same results, your question seems to be what is an efficient way of getting the last results in each group in MySQL. If you are working with huge amounts of data and assuming you are using InnoDB with even the latest versions of MySQL (such as 5.7.21 and 8.0.4-rc) then there might not be an efficient way of doing this.

We sometimes need to do this with tables with even more than 60 million rows.

For these examples I will use data with only about 1.5 million rows where the queries would need to find results for all groups in the data. In our actual cases we would often need to return back data from about 2,000 groups (which hypothetically would not require examining very much of the data).

I will use the following tables:

CREATE TABLE temperature(
  recordedTimestamp TIMESTAMP NOT NULL, 
  recordedValue INT NOT NULL,
  INDEX groupIndex(groupID, recordedTimestamp), 


The temperature table is populated with about 1.5 million random records, and with 100 different groups. The selected_group is populated with those 100 groups (in our cases this would normally be less than 20% for all of the groups).

As this data is random it means that multiple rows can have the same recordedTimestamps. What we want is to get a list of all of the selected groups in order of groupID with the last recordedTimestamp for each group, and if the same group has more than one matching row like that then the last matching id of those rows.

If hypothetically MySQL had a last() function which returned values from the last row in a special ORDER BY clause then we could simply do:

  last( AS id, 
  last(t1.recordedTimestamp) AS recordedTimestamp, 
  last(t1.recordedValue) AS recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID =
ORDER BY t1.recordedTimestamp,
GROUP BY t1.groupID;

which would only need to examine a few 100 rows in this case as it doesn't use any of the normal GROUP BY functions. This would execute in 0 seconds and hence be highly efficient. Note that normally in MySQL we would see an ORDER BY clause following the GROUP BY clause however this ORDER BY clause is used to determine the ORDER for the last() function, if it was after the GROUP BY then it would be ordering the GROUPS. If no GROUP BY clause is present then the last values will be the same in all of the returned rows.

However MySQL does not have this so let's look at different ideas of what it does have and prove that none of these are efficient.

Example 1

SELECT, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON = (
  FROM temperature t2 
  WHERE t2.groupID =
  ORDER BY t2.recordedTimestamp DESC, DESC

This examined 3,009,254 rows and took ~0.859 seconds on 5.7.21 and slightly longer on 8.0.4-rc

Example 2

SELECT, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
  SELECT max( AS id   
  FROM temperature t2
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID =
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
) t5 ON =;

This examined 1,505,331 rows and took ~1.25 seconds on 5.7.21 and slightly longer on 8.0.4-rc

Example 3

SELECT, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM temperature t1
  SELECT max( AS id   
  FROM temperature t2
    SELECT t3.groupID, max(t3.recordedTimestamp) AS recordedTimestamp
    FROM selected_group g
    INNER JOIN temperature t3 ON t3.groupID =
    GROUP BY t3.groupID
  ) t4 ON t4.groupID = t2.groupID AND t4.recordedTimestamp = t2.recordedTimestamp
  GROUP BY t2.groupID
ORDER BY t1.groupID;

This examined 3,009,685 rows and took ~1.95 seconds on 5.7.21 and slightly longer on 8.0.4-rc

Example 4

SELECT, t1.groupID, t1.recordedTimestamp, t1.recordedValue
FROM selected_group g
INNER JOIN temperature t1 ON = (
  SELECT max(
  FROM temperature t2 
  WHERE t2.groupID = AND t2.recordedTimestamp = (
      SELECT max(t3.recordedTimestamp)
      FROM temperature t3 
      WHERE t3.groupID =

This examined 6,137,810 rows and took ~2.2 seconds on 5.7.21 and slightly longer on 8.0.4-rc

Example 5

SELECT, t1.groupID, t1.recordedTimestamp, t1.recordedValue
    row_number() OVER (
      PARTITION BY t2.groupID ORDER BY t2.recordedTimestamp DESC, DESC
    ) AS rowNumber
  FROM selected_group g 
  INNER JOIN temperature t2 ON t2.groupID =
) t1 WHERE t1.rowNumber = 1;

This examined 6,017,808 rows and took ~4.2 seconds on 8.0.4-rc

Example 6

SELECT, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
    last_value( OVER w AS id, 
    last_value(t2.recordedTimestamp) OVER w AS recordedTimestamp, 
    last_value(t2.recordedValue) OVER w AS recordedValue
  FROM selected_group g
  INNER JOIN temperature t2 ON t2.groupID =
    PARTITION BY t2.groupID 
    ORDER BY t2.recordedTimestamp, 
) t1
GROUP BY t1.groupID;

This examined 6,017,908 rows and took ~17.5 seconds on 8.0.4-rc

Example 7

SELECT, t1.groupID, t1.recordedTimestamp, t1.recordedValue 
FROM selected_group g
INNER JOIN temperature t1 ON t1.groupID =
LEFT JOIN temperature t2 
  ON t2.groupID = 
  AND (
    t2.recordedTimestamp > t1.recordedTimestamp 
    OR (t2.recordedTimestamp = t1.recordedTimestamp AND >
ORDER BY t1.groupID;

This one was taking forever so I had to kill it.

@Paul Spiegel 2019-12-10 14:46:52

This is a different problem. And the solution is a huge UNION ALL query.

@Yoseph 2019-12-12 10:06:00

@PaulSpiegel I guess you are joking about the huge UNION ALL. Besides the fact that one would need to know all of the selected groups in advance, and that with 2,000 selected groups that would be an incredibly huge query, it would perform even worse than the fastest example above, so no, that would not be a solution.

@Paul Spiegel 2019-12-12 17:40:50

I'm absolutely serious. I've tested that in the past with a couple of hundred groups. When you need to handle ties in big groups, UNION ALL is the only way in MySQL to force an optimal execution plan. SELECT DISTINCT(groupID) is fast and will give you all data that you need to construct such a query. You should be fine with the query size as long as it doesn't exceed max_allowed_packet, which defaults to 4MB in MySQL 5.7.

@Song Zhengyi 2018-03-10 20:33:11

An approach with considerable speed is as follows.

FROM messages a
WHERE Id = (SELECT MAX(Id) FROM messages WHERE a.Name = Name)


Id  Name    Other_Columns
3   A   A_data_3
5   B   B_data_2
6   C   C_data_1

@Rick James 2020-01-28 00:51:44

This assumes id is ordered the way you need it. In the general case some other column is needed.

@Abhishek Yadav 2017-06-08 18:49:57

Here is my solution:


@Paul Spiegel 2019-12-10 14:31:26

This doesn't return the latest message per name. And it's just an overcomplicated version of SELECT NAME, MAX(MESSAGES) MESSAGES FROM MESSAGE GROUP BY NAME.

@Rick James 2020-01-28 00:44:06

Furthermore, this formulation is grossly inefficient.

@Azathoth 2016-11-30 10:50:40

How about this:

FROM messages
ORDER BY name, id DESC;

I had similar issue (on postgresql tough) and on a 1M records table. This solution takes 1.7s vs 44s produced by the one with LEFT JOIN. In my case I had to filter the corrispondant of your name field against NULL values, resulting in even better performances by 0.2 secs

@huuang 2016-06-18 14:12:25

select * from messages group by name desc

@Ullas 2015-11-19 04:36:11

If you want the last row for each Name, then you can give a row number to each row group by the Name and order by Id in descending order.


SELECT t1.Id, 
     SELECT Id, 
        CASE Name WHEN @curA 
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curA := Name END 
    ) + 1 AS rn 
    FROM messages t, 
    (SELECT @curRow := 0, @curA := '') r 
    ORDER BY Name,Id DESC 
WHERE t1.rn = 1

SQL Fiddle

@Shrikant Gupta 2015-09-28 09:07:12

You can take view from here as well.!9/ef42b/9


SELECT d1.ID,Name,City FROM Demo_User d1



@bikashphp 2014-10-21 14:08:16

Hi @Vijay Dev if your table messages contains Id which is auto increment primary key then to fetch the latest record basis on the primary key your query should read as below:

SELECT m1.* FROM messages m1 INNER JOIN (SELECT max(Id) as lastmsgId FROM messages GROUP BY Name) m2 ON m1.Id=m2.lastmsgId

@CORSAIR 2019-04-10 12:01:29

This one the fastest i found

@jeet singh parmar 2014-04-11 06:55:00

  GROUP BY column1) 
ORDER BY column1 ;

@janfoeh 2014-05-04 11:57:54

Could you elaborate a bit on your answer? Why is your query preferrable to Vijays original query?

@M Khalid Junaid 2014-03-29 14:51:01

Here is another way to get the last related record using GROUP_CONCAT with order by and SUBSTRING_INDEX to pick one of the record from the list

      ORDER BY `Id` DESC 
      SEPARATOR '||'
  ) Other_Columns 
GROUP BY `Name` 

Above query will group the all the Other_Columns that are in same Name group and using ORDER BY id DESC will join all the Other_Columns in a specific group in descending order with the provided separator in my case i have used || ,using SUBSTRING_INDEX over this list will pick the first one

Fiddle Demo

@Rick James 2020-01-28 00:55:33

Be aware that group_concat_max_len limits how many rows you can handle.

@user942821 2012-03-31 14:44:05

I've not yet tested with large DB but I think this could be faster than joining tables:

SELECT *, Max(Id) FROM messages GROUP BY Name

@harm 2014-07-03 15:05:51

This returns arbitrary data. In other words there returned columns might not be from the record with MAX(Id).

@Nicola 2015-04-08 09:24:07

Useful to select the max Id from a set of record with WHERE condition : "SELECT Max(Id) FROM Prod WHERE Pn='" + Pn + "'" It returns the max Id from a set of records with same Pn.In c# use reader.GetString(0) to get the result

@JYelton 2012-02-20 21:46:38

I arrived at a different solution, which is to get the IDs for the last post within each group, then select from the messages table using the result from the first query as the argument for a WHERE x IN construct:

SELECT id, name, other_columns
FROM messages
    SELECT MAX(id)
    FROM messages
    GROUP BY name

I don't know how this performs compared to some of the other solutions, but it worked spectacularly for my table with 3+ million rows. (4 second execution with 1200+ results)

This should work both on MySQL and SQL Server.

@Samuel Åslund 2016-04-22 11:58:45

Just make sure you have an index on (name, id).

@anwerj 2016-12-23 07:40:49

Much better that self joins

@Humphrey 2018-02-23 07:48:08

I learned something from you that is a good job and this query is faster

@Teja 2011-11-18 20:19:44

The below query will work fine as per your question.

 SELECT SUBSTR(Others_data,1,2),MAX(Others_data) AS Max_Others_data
) M2
WHERE M1.Others_data = M2.Max_Others_data
ORDER BY Others_data;

@Pro Web Design 2011-07-15 02:05:08

Try this:

SELECT jos_categories.title AS name,
       joined .catid,
       joined .title,
       joined .introtext
FROM   jos_categories
                   FROM   (SELECT `title`,
                           FROM   `jos_content`
                           WHERE  `sectionid` = 6
                           ORDER  BY `id` DESC) AS yes
                   GROUP  BY `yes`.`catid` DESC
                   ORDER  BY `yes`.`created` DESC) AS joined
         ON( joined.catid = )  

@Simon 2010-10-08 01:10:11

Is there any way we could use this method to delete duplicates in a table? The result set is basically a collection of unique records, so if we could delete all records not in the result set, we would effectively have no duplicates? I tried this but mySQL gave a 1093 error.

 FROM messages m1 LEFT JOIN messages m2  
 ON ( = AND <  

Is there a way to maybe save the output to a temp variable then delete from NOT IN (temp variable)? @Bill thanks for a very useful solution.

EDIT: Think i found the solution:

CREATE Temporary table UniqueIDs (id Int(11)); 

    (SELECT T1.ID FROM Table T1 LEFT JOIN Table T2 ON 
    (T1.Field1 = T2.Field1 AND T1.Field2 = T2.Field2 #Comparison Fields  
    AND T1.ID < T2.ID) 


@Steve Kass 2009-08-21 17:26:12

Here are two suggestions. First, if mysql supports ROW_NUMBER(), it's very simple:

WITH Ranked AS (
  SELECT Id, Name, OtherColumns,
    ) AS rk
  FROM messages
  SELECT Id, Name, OtherColumns
  FROM messages
  WHERE rk = 1;

I'm assuming by "last" you mean last in Id order. If not, change the ORDER BY clause of the ROW_NUMBER() window accordingly. If ROW_NUMBER() isn't available, this is another solution:

Second, if it doesn't, this is often a good way to proceed:

  Id, Name, OtherColumns
FROM messages
  SELECT * FROM messages as M2
  WHERE M2.Name = messages.Name
  AND M2.Id > messages.Id

In other words, select messages where there is no later-Id message with the same Name.

@Bill Karwin 2009-08-21 17:37:56

MySQL doesn't support ROW_NUMBER() or CTE's.

@Rick James 2020-01-28 00:52:55

MySQL 8.0 (and MariaDB 10.2) now support ROW_NUMBER() and CTEs.

@Eric 2009-08-21 17:06:42

Use your subquery to return the correct grouping, because you're halfway there.

Try this:

    messages a
    inner join 
        (select name, max(id) as maxid from messages group by name) as b on = b.maxid

If it's not id you want the max of:

    messages a
    inner join 
        (select name, max(other_col) as other_col 
         from messages group by name) as b on =
        and a.other_col = b.other_col

This way, you avoid correlated subqueries and/or ordering in your subqueries, which tend to be very slow/inefficient.

@Eric Simonton 2015-08-21 13:48:54

Note a caveat for the solution with other_col: if that column is not unique you may get multiple records back with the same name, if they tie for max(other_col). I found this post that describes a solution for my needs, where I need exactly one record per name.

@tom10271 2015-09-04 02:59:40

In some situations you can only use this solution but ont the accepted one.

@Timo 2018-04-30 14:56:43

In my experience, it is grouping the whole damn messages table that tends to be slow/inefficient! In other words, note that the subquery requires a full table scan, and does a grouping on that to boot... unless your optimizer is doing something that mine is not. So this solution depends heavily on holding the entire table in memory.

@Rick James 2020-01-15 23:30:19

Those would benefit from INDEX(name, id) and INDEX(name, other_col)

Related Questions

Sponsored Content

11 Answered Questions

[SOLVED] How to Join to first row

20 Answered Questions

[SOLVED] Get top 1 row of each group

2 Answered Questions

[SOLVED] Using group by on multiple columns

16 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

47 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

7 Answered Questions

39 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

11 Answered Questions

[SOLVED] MySQL - UPDATE query based on SELECT Query

Sponsored Content