CODEWITHSUNDEEP
mysqlsql
luis.ap.uyen
luis.ap.uyen

Reputation: 1364

Get most recent record from one-to-many relationship

As I say in the title, having this schema:

CREATE TABLE IF NOT EXISTS `services` (
  `id` int(6) unsigned NOT NULL,
  `description` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `services` (`id`, `description`) VALUES
  ('1', 'Water flood from kitchen'),
  ('2', 'Light switch burnt');


CREATE TABLE IF NOT EXISTS `visits` (
  `id` int(6) unsigned NOT NULL,
  `date` DATETIME NOT NULL,
  `description` varchar(200) NOT NULL,
  `worker` varchar(200) NOT NULL,
  `services_id` int(6) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `visits` (`id`, `date`, `description`, `worker`, `services_id`) VALUES
  ('1', '2018-12-10 16:00:00', 'Find and stop leak', 'Thomas', '1'),
  ('2', '2018-12-11 09:00:00', 'Change broken pipe', 'Bob', '1'),
  ('3', '2018-12-10 19:00:00', 'Change light switch', 'Alfred', '2'),
  ('4', '2018-12-11 10:00:00', 'Paint wall blackened by shortcircuit', 'Ryan', '2');

I need to get the most recent visit, date-wise, for each service.

In this example, I would get:

'1', '2018-12-10 16:00:00', 'Find and stop leak', 'Thomas', '1'
'3', '2018-12-10 19:00:00', 'Change light switch', 'Alfred', '2'

How would you do it? I'm struggling to get a solution.

Here's the SQLFiddle:

http://sqlfiddle.com/#!9/3ca219

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271221

I would suggest a correlated subquery:

select v.*
from visits v
where v.date = (select max(v2.date)
                from visits v2
                where v2.services_id = v.services_id
               );

With an index on visits(services_id, date), this should be as fast or faster than other approaches.

Upvotes: 2

Related Questions