Skip to content

Instantly share code, notes, and snippets.

@webdevilopers

webdevilopers/dump.sql

Last active August 29, 2015 14:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save webdevilopers/3a82175f6f4743e707b0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save webdevilopers/3a82175f6f4743e707b0 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL - Join row to get latest change in one single row
Raw
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');
Raw
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
*/
Raw
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
*/
Raw
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
*/
Raw
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
*/
Raw
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
*/
Raw
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
*/
@webdevilopers
Copy link
Author

webdevilopers commented Aug 11, 2014

Related issues:

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment