I created this SQL fiddle with the sample data
The sample data is this:
--
-- Table structure for table `dogs`
--
CREATE TABLE IF NOT EXISTS `dogs` (
`version_stamp` datetime NOT NULL,
`dog_id` int(11) unsigned NOT NULL,
`dog_name` varchar(30) DEFAULT NULL,
`dog_color` varchar(30) DEFAULT NULL,
PRIMARY KEY (`version_stamp`,`dog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `dogs`
--
INSERT INTO `dogs` (`version_stamp`, `dog_id`, `dog_name`, `dog_color`) VALUES
('2014-09-28 09:30:24', 19849, 'Ruediger', 'black'),
('2014-09-25 02:15:23', 19850, NULL, 'white'),
('2014-09-25 02:16:41', 19850, 'Bello', NULL),
('2014-09-26 02:15:23', 19850, NULL, 'brown'),
('2014-09-27 02:16:41', 19850, 'Dieter', NULL);
What I want, is the newest version of a row, e.g. dog
row.
Say I enter the dog_id
19850
with date time stamp
2014-09-26 02:15:24
Then I want to get :
Bello brown
When I use a different timestamp, say:
2099-12-12 12:12:12
I want to get
Dieter, brown
Is there a way to do this in SQL?
Best Answer
Something like this should work if you specify the dog_id and version_stamp. It's going to SELECT the top dog_name and dog_color ORDER BY the latest version_stamp. Then you roll those up based on dog_id using MAX().
Fiddle example.