469,904 Members | 1,920 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

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

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

Jul 19 '05 #1
0 3536

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by nbrcrunch | last post: by
reply views Thread by TJS | last post: by
4 posts views Thread by John | last post: by
2 posts views Thread by Bruce A. Julseth | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.