search for only the newest version of each record in a mysql table using date as version field

I have searched and I am unable to find an answer to this. Other links here exist, but they do not seem to be relevant as they reference only one or two tables, a statically known date that will be equal for all records, or are marked solved with no real answer was given.

I have the following tables:

-- list of items
CREATE TABLE `a` (
  `a_id` bigint(20) unsigned NOT NULL auto_increment primary key,
  `a_name` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=176 DEFAULT CHARSET=utf8;

-- sample data
insert into a(a_id,a_name) values('1','apple');
insert into a(a_id,a_name) values('2','banana');
insert into a(a_id,a_name) values('3','cheese');
insert into a(a_id,a_name) values('4','bucket of lard');

-- places
CREATE TABLE  `b` (
  `b_id` bigint(20) unsigned NOT NULL auto_increment primary key,
  `b_data` varchar(20) not null 
)ENGINE=InnoDB AUTO_INCREMENT=629 DEFAULT CHARSET=utf8;

insert into b(b_id,b_data) values('1','floor');
insert into b(b_id,b_data) values('2','counter top');
insert into b(b_id,b_data) values('3','bed')开发者_运维问答;
insert into b(b_id,b_data) values('4','table');

-- item data showing item A is in place B, with extra data.
CREATE TABLE  `c` (
  `c_id` bigint(20) unsigned NOT NULL auto_increment primary key,

  `c_a_id` bigint(20) unsigned NOT NULL,
 FOREIGN KEY (c_a_id) REFERENCES a(a_id),

  `c_b_id` bigint(20) unsigned NOT NULL,
 FOREIGN KEY (c_b_id) REFERENCES b(b_id),

  `c_value` decimal(60,2) NOT NULL default '0.00',

  `c_when_created` bigint(20) unsigned NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=129886 DEFAULT CHARSET=utf8;

-- sample data (by no means complete!)
 insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('1','1','1', '3.14578', UNIX_TIMESTAMP('2011-02-22 03:00:00'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('2','2','1', '14578', UNIX_TIMESTAMP('2011-02-22 03:00:01'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('3','3','1', '4578', UNIX_TIMESTAMP('2011-02-22 03:00:02'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('4','4','2', '578', UNIX_TIMESTAMP('2011-02-22 03:50:03'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('1','1','4', '78', UNIX_TIMESTAMP('2011-02-22 03:00:00'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('2','2','3', '8', UNIX_TIMESTAMP('2011-02-22 03:01:01'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('3','3','2', '3', UNIX_TIMESTAMP('2011-02-22 03:00:42'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('4','4','1', '31', UNIX_TIMESTAMP('2011-02-22 03:05:03'));
insert into c(c_id, c_a_id, c_b_id, c_value, c_when_created )
 values('1','1','1', '77', UNIX_TIMESTAMP('2006-03-27 03:10:00'));

Now the problem is this: I want to search for items from 'a' that are in table 'c' as being in place table 'b', but only return the records that are not out of date by virtue of having a newer record exist that references this data. In my mind, the field in 'c' that has 'a' and 'b' listed with the newest date is the only valid field , the rest are old and out of date and kept for historical data tracking and reporting.

Example given the following data in c if I looked for everything in the :

 insert into c(c_a_id, c_b_id, c_value, c_when_created )
 values('1','1', '3.14578', UNIX_TIMESTAMP('2011-02-22 03:00:00'));
insert into c(c_a_id, c_b_id, c_value, c_when_created )
 values('2','1', '14578', UNIX_TIMESTAMP('2011-02-22 03:00:01'));
insert into c(c_a_id, c_b_id, c_value, c_when_created )
 values('1','1', '4578', UNIX_TIMESTAMP('2011-02-22 03:00:02'));

.. I would expect to only get only the last two rows with dates '2011-02-22 03:00:01' and '2011-02-22 03:00:02' as the '2011-02-22 03:00:00' row would be considered 'old' since the '2011-02-22 03:00:02' row exists.

IE, if 4 records exist for a_id being equal to '1' and I search for that record in c expecting b_id to be 2, I only want to get the one record that has the date being the most recent.

Adding data to c that references a and b in a nice way has been a delight and kept the number of items in a and b down to a manageable level; but getting it back out has proven very difficult. as I only want to return records that do not have a newer record in the system.

If I understand your problem correctly, you could do a select ordering by date descending and limit the result to 1

select b.*, c.value, c.date from b
inner join c on c_b_id = b.b_id
order by c.date desc
limit 1