query for revision control system

[redbullmarky]

Hi all

 

I'm working on a mini revision control system. Each page can have a number of slots to insert data, each slot can have its own name and slots can be of different types. If my table structure looks a bit like this:

 

 

  `id` int(10) unsigned NOT NULL auto_increment,
  `public` enum('y','n') default 'n',
  `page_id` int(10)
  `type` enum('type1','type2')
  `name` varchar(150)
  `data`
  PRIMARY KEY  (`id`)

 

how can I, in one query, pull out all the latest revisions (i'm using the primary key for this - ie, ORDER BY id DESC). in the event that there are two or more records with the same 'page_id', 'type' and 'name', i need to pull out the one with the highest ID.

 

It maybe more simple than i'm making it out to be, but i'm a little rusty with my SQL these days...Hope my question makes sense

 

Target machine is MySQL 3.23 if that changes anything...

 

Cheers

Mark

[redbullmarky]

this does the job (or seems to):

 

$query = "SELECT type, name, CONCAT(object_id,type,name) as uniquefield, id, data FROM 
			(SELECT * FROM object_data ORDER BY id DESC) as subq
			WHERE object_id = $object_id
				AND public = 'y'
			GROUP BY uniquefield";

 

but my reservations:

1, it seems a bit 'hacky'

2, it uses subqueries - i'm pretty sure this is probably slower, and also no idea if this works on 3.23