Query

[kevosurge]

Heeey! I have a table that is used to store information about files with multiple versions. I'm trying to code a system that will watermark files with "OUTDATED" that are NOT the current version directly before returning the file to the end user (creates a temporary file with the watermark). I'm supplying my PHP function only with the ID of the file attempting to be accessed. I need a query that uses the supplied ID to compare it's "name" to other rows with the same name to determine if the file ID being accessed is the latest version. My table columns are: id, name, start_date. Once the query works, then I can use PHP to determine if any rows were returned and thusly displaying or not displaying the watermark. Here's some sample data:

 

TABLE

"id" - "name" - "start_date"

023 - File A - 2009-01-19

075 - File A - 2009-03-24

101 - File B - 2009-04-12

213 - File A - 2009-06-20

 

File attempting to be accessed: 075 - "File A"

So, basically, I need the query to say... is 075 is the latest of the files named "File A" based on start_date?

[kickstart]

Hi

 

Not quite sure this is what you want.

 

This will get you the latest record which has the same name as id 075.

 

SELECT c.*
FROM (SELECT `name`, MAX(start_date) AS LatestStartDate FROM SomeTable GROUP BY `name`) a
INNER JOIN (SELECT `name` FROM SomeTable WHERE id = '075') b ON a.`name` = b.`name`
INNER JOIN SomeTable c ON b.`name` = c.`name` AND a.LatestStartDate = c.start_date

 

Basically joining the table against itself a few times, once to get the name for the id, again to get the latest start_date for that and once again to bring back all the details for that latest one.

 

Not it will bring back 2 records if there are 2 files with the same name and start date.

 

All the best

 

Keith