How to write query to select the Max(version) for each unique file_name record?

[Tanamon]

Hello All,

I am a MySQL newbie trying to write a query that selects file_name records
possessing the highest numbered version for that unique file_name. I show
sample data and two trial queries below. Logically I want to use
max(version) as a constraint in a Where Clause. However, the max() function
is not allowed directly in a where clause.

I have contemplated a second table to track the max version for each file
name. I would like to structure the data in an efficient manner for query
performance when the data set grows to many thousands of unique file_name
records with many hundreds of versions each.

Any guidance will be appreciated.

David Oberlitner
------------------------------------------------------------------------
----------------

Sample data:

mysql> select * from test;
+----+-----------+---------+
| id | file_name | version |
+----+-----------+---------+
| 1 | fone | 1 |
| 2 | ftwo | 1 |
| 3 | fone | 2 |
| 4 | fone | 3 |
| 5 | fthree | 1 |
| 6 | ffour | 1 |
| 7 | ftwo | 2 |
| 8 | ffour | 2 |
+----+-----------+---------+

The query below gets close in that it returns each file name and its
max(version), however it returns the ID field associated with the first
record instance of file_name and not the ID associated with the
max(version) instance of the file_name record.

mysql> select id, file_name, max(version) from test group by file_name;
+----+-----------+--------------+
| id | file_name | max(version) |
+----+-----------+--------------+
| 6 | ffour | 2 |
| 1 | fone | 3 |
| 5 | fthree | 1 |
| 2 | ftwo | 2 |
+----+-----------+--------------+
4 rows in set (0.02 sec)

The query below returns the empty set.

mysql> select id, file_name, max(version) from test group by file_name
having max(version);
Empty set (0.00 sec)

Additionally,
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/my***********...ie.nctu.edu.tw