Demaestro - 6:02 pm on Jul 28, 2008 (gmt 0)

Weird.... tweak mine a bit.... It should work... here is the logic thought out loud.

Tester
id ¦ file_name ¦ folder ¦ int_time
----+------------+--------+-----------
1 ¦ name_1.gif ¦ docs ¦ 807281350
2 ¦ name_1.gif ¦ docs ¦ 807281353
3 ¦ name_2.gif ¦ docs ¦ 807281350
4 ¦ name_2.gif ¦ docs ¦ 807281355

Write a query that gets the max int_time for each unque file_name.......

******************
testing=> select max(int_time), file_name from tester group by file_name;

max ¦ file_name
-----------+------------
807281353 ¦ name_1.gif
807281355 ¦ name_2.gif
(2 rows)
******************
^^^You should get this for that query^^^

Now that we have the max int_time for each unique file_name we now want to find their ids.... so add the above to another query to get the ids by matching on file_name and int_time... so like this

******************
testing=> select
tester.*
from
tester, (select max(int_time) as int_time, file_name from tester group by file_name) as max_times
where
tester.folder = 'docs'
and max_times.int_time = tester.int_time
and max_times.file_name = tester.file_name;

id ¦ file_name ¦ folder ¦ int_time
----+------------+--------+-----------
2 ¦ name_1.gif ¦ docs ¦ 807281353
4 ¦ name_2.gif ¦ docs ¦ 807281355
(2 rows)
******************

Mind you this is in Postgres but I made this table and this is what I am getting... the syntax may differ in MySQL, I assume that select statements are allowed. It could be that syntax doesn't work but if you break it into a view then it might

To do what I did in a view logic first create view based on that first query (as long as you get the same results I did)

CREATE VIEW view_name AS
select max(int_time), file_name
from tester
group by file_name

This view should have all the max int_time for each unique file_name... so then select from the view like this.

select
tbl.*
from
tbl, view_name
where
tbl.folder = 'docs'
and view_name.int_time = tbl.int_time
and view_name.file_name = tbl.file_name;