small challange - php and sql query

[_SuperString]

Hi,

OK, here is a small challenge for you (I cant find the best solution).

Im using PHP 4.3.4 with MySQL version 3* (doesnt support sub-queries).

I have a table with the data in the following structure:

id     filename     version     datetime
--     -----------     ---------     -----------
1     test.php      0.1           2005-03-21 12:12:59
2     test.php      0.2           2005-03-21 12:14:59
3     other.php    0.1           2003-03-21 12:12:59
4     test.php      0.3           2005-03-22 12:11:23
.... etc

I need to select all fields from the rows with the highest 'version' per filename - so from the above data I need to select only rows with the id of 3 and 4 (as these have the highest version number against each filename)

Any solutions?

Right now I am looping the results all a standard select (selecting everything), ordering the SQL by version, and creating a flag of the filename so only one will be displayed..... but this is not flexible enough for other things I need to do - for example if I want to order by date!!

cheers

[mrhappiness]

SELECT 
  t1.*
FROM 
  yourtable t1
  LEFT JOIN yourtable dummy
    ON 
      t.filename = dummy.filename
      AND
      t1.version < dummy.version
WHERE
  dummy.version IS NULL

give it a try

[devinemke]

SELECT id, filename, MAX(version) as max_version FROM table GROUP BY filename

...will give you the id for the highest version for each filename, you can then do a query for those specific ids

SELECT * FROM table WHERE id IN([the results from the prvious query])

[_SuperString]

Thanks guys.... I'm almost certain devinemke's solution will work great..

thanks again!!