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/[EMAIL PROTECTED]
