0

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?

1

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.

SELECT
   a.dog_id
  ,MAX(a.version_stamp) AS 'Version Stamp'
  ,MAX(a.dog_name) AS 'Dog Name'
  ,MAX(a.dog_color) AS 'Dog Color'
FROM 
   (
    SELECT
      *
    FROM dogs
    WHERE version_stamp <= '2099-12-12 12:12:12'
    AND dog_id = 19850
    AND dog_name IS NOT NULL
    UNION
    SELECT
      *
    FROM dogs
    WHERE version_stamp <= '2099-12-12 12:12:12'
    AND dog_id = 19850
    AND dog_name IS NULL
    ORDER BY version_stamp DESC
    LIMIT 2 
   ) a
GROUP BY 
   a.dog_id

DOG_ID  VERSION STAMP   DOG NAME    DOG COLOR

19850   September, 27 2014 02:16:41+0000    Dieter  brown
1

You can use 2 simple subqueries, one for each column:

SELECT
  ( SELECT dog_name
    FROM dogs
    WHERE dog_id = p.dog_id
      AND version_stamp <= p.version_stamp
      AND dog_name IS NOT NULL
    ORDER BY version_timestamp DESC
      LIMIT 1
  ) AS dog_name,

  ( SELECT dog_color
    FROM dogs
    WHERE dog_id = p.dog_id
      AND version_stamp <= p.version_stamp
      AND dog_color IS NOT NULL
    ORDER BY version_timestamp DESC
      LIMIT 1
  ) AS dog_color 
FROM
    ( SELECT                                        -- your parameters
          19850                 AS dog_id,
          '2099-12-12 12:12:12' AS version_stamp
    ) AS p ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.