MySQL - Join row to get latest change in one single row

dump.sql

--
-- Database: `webdevilopers`
--

-- --------------------------------------------------------

--
-- Table structure for table `contracts`
--

CREATE TABLE IF NOT EXISTS `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Dumping data for table `contracts`
--

INSERT INTO `contracts` (`id`, `number`) VALUES
(1, 111111),
(2, 222222);

-- --------------------------------------------------------

--
-- Table structure for table `contract_states`
--

CREATE TABLE IF NOT EXISTS `contract_states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contract_id` int(11) NOT NULL,
  `stateName` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `contract_id` (`contract_id`,`createdAt`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5 ;

--
-- Dumping data for table `contract_states`
--

INSERT INTO `contract_states` (`id`, `contract_id`, `stateName`, `createdAt`) VALUES
(1, 1, 'opened', '2014-08-01 00:00:00'),
(2, 1, 'pending', '2014-08-04 00:00:00'),
(3, 1, 'closed', '2014-08-10 00:00:00'),
(4, 2, 'opened', '2014-08-11 00:00:00');

multi-rows_ordererd.sql

SELECT c.id, c.number, cs.createdAt, cs.stateName
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE 1
ORDER BY cs.createdAt DESC

/*
id 	number 	createdAt 	        stateName
2 	222222 	2014-08-11 00:00:00 	opened
1 	111111 	2014-08-10 00:00:00 	closed
1 	111111 	2014-08-04 00:00:00 	pending
1 	111111 	2014-08-01 00:00:00 	opened
*/

singlerows_max_grouped.sql

SELECT c.id, c.number, MAX(cs.createdAt), cs.stateName
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE 1
GROUP BY c.id

/*
id 	number 	MAX(cs.createdAt) 	stateName
1 	111111 	2014-08-10 00:00:00 	opened
2 	222222 	2014-08-11 00:00:00 	opened
*/

singlerows_max_subquery.sql

SELECT c.id, c.number, cs.createdAt, cs.stateName,
(
    SELECT MAX(cs2.createdAt)
    FROM `contract_states` `cs2`
    WHERE `cs2`.`contract_id` = `c`.`id`
) AS lastCreatedAt
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE 1


/*
id 	number 	createdAt 	        stateName 	lastCreatedAt
1 	111111 	2014-08-01 00:00:00 	opened 	        2014-08-10 00:00:00
1 	111111 	2014-08-04 00:00:00 	pending 	2014-08-10 00:00:00
1 	111111 	2014-08-10 00:00:00 	closed 	        2014-08-10 00:00:00
2 	222222 	2014-08-11 00:00:00 	opened 	        2014-08-11 00:00:00
*/

singlerows_max_subquery_having.sql

SELECT c.id, c.number, cs.createdAt, cs.stateName,
(
  SELECT MAX(cs2.createdAt)
  FROM `contract_states` `cs2`
  WHERE `cs2`.`contract_id` = `c`.`id`
) AS lastCreatedAt
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE 1
HAVING cs.createdAt = lastCreatedAt

/*
id 	number 	createdAt 	        stateName 	lastCreatedAt
1 	111111 	2014-08-10 00:00:00 	closed 	        2014-08-10 00:00:00
2 	222222 	2014-08-11 00:00:00 	opened 	        2014-08-11 00:00:00
*/

singlerows_max_where_subquery.sql

SELECT c.id, c.number, cs.createdAt, cs.stateName
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE cs.createdAt = (
  SELECT MAX(cs2.createdAt)
  FROM `contract_states` `cs2`
  WHERE `cs2`.`contract_id` = `c`.`id`
)

/*
id 	number 	createdAt 	        stateName
1 	111111 	2014-08-10 00:00:00 	closed
2 	222222 	2014-08-11 00:00:00 	opened
*/

singlerows_orderBy_max.sql

SELECT c.id, c.number, cs.createdAt, cs.stateName
FROM `contracts` `c`
JOIN `contract_states` `cs` ON `c`.`id` = `cs`.`contract_id`
WHERE 1
GROUP BY c.id
ORDER BY MAX( cs.createdAt )

/*
id 	number 	createdAt 	        stateName
1 	111111 	2014-08-01 00:00:00 	opened
2 	222222 	2014-08-11 00:00:00 	opened
*/

Related issues:

• APY/APYDataGridBundle#592