July July - 1 month ago 8
SQL Question

How to write a SQL query that can get below result?

Below is my table ddl and data:

CREATE TABLE `Msg` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`session_id` bigint(20) unsigned NOT NULL,
`create_date` datetime NOT NULL,
`content` varchar(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

/*Data for the table `Msg` */

insert into `Msg`(`id`,`session_id`,`create_date`,`content`) values (1,11,'2016-10-01 20:30:34','hello 1'),(2,11,'2016-10-02 20:30:47','hello 2'),(3,11,'2016-10-11 20:31:07','hello 3'),(4,12,'2016-10-04 20:31:22','world 1'),(5,12,'2016-10-26 20:31:42','world 2'),(6,13,'2016-10-05 20:31:58','good 1'),(7,13,'2016-10-28 20:32:16','good 2'),(8,13,'2016-10-07 20:39:44','good 3');


How to get Msg records that:


  1. session_id is in (12, 13, 14), and

  2. for each session, select only one msg record that has the max create_date



As in the example, the result should be the two rows with id 7 and 5.

Answer

Here is a typical way to do this:

select m.*
from msg m
where m.id = (select max(m2.id) from msg m2 where m2.session_id = m.session_id);

Note: although you are asking for the most recent date, this uses the id instead. It is an auto-incremented id, so I made the reasonable assumption that bigger ids imply bigger dates. Of course, you can replace id in the where conditions with create_date.

If you have a largish table, you'll want an index on msg(session_id, id) (or msg(session_id, create_date) if you use that field instead).

Comments