Row Level Revision Queries MySQL.

Question

I currently implement some kind of version control for values in a table. I have a composite primary key between bookingId and revision. I want to be able to select all records from this table which are the head revision? I am not sure what I should be doing.

[ bookingDetailsTable ] :

• [ bookingId ]
• [ revision ]
• [ name ]
• etc...

SELECT * from bookingDetailsTable group by bookingId selects the first revision however I want to be able to select the HEAD revision for each booking.

I should add that I cannot do something like this cause the WHERE would only select revisions of which are the highest in the entire table.

SELECT * from bookingDetailsTable where revision = (
                                    select max(revision) from bookingDetailsTable
                                   )
GROUP BY bookingId 

My Current SELECT

Here is my current select, it has three joins. The REVISION control is limited to the details table. So I want the join to only use the highest revision from this table: module_bookings_bookings_details

SELECT `b`.*, `b`.`id` AS `bookingId`, `d`.*, `c`.*, `p`.* FROM `module_bookings_bookings` AS `b`
 INNER JOIN `module_bookings_bookings_details` AS `d` ON b.id = d.bookingId
 INNER JOIN `module_bookings_clients` AS `c` ON b.clientId = c.id
 LEFT JOIN `module_bookings_property` AS `p` ON d.propertyId = p.id GROUP BY `b`.`id`

Answer 1

Here is one solution I have found.

Query using self join.

select * from module_bookings_bookings_details as d1 
left join module_bookings_bookings_details as d2 on d1.bookingId = d2.bookingId
      and d1.revision < d2.revision 
where  d2.revision IS NULL

Source: http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

Although I am not sure how to do that query using the join.