Databases Forum

My expected result should be:

Small error in my original example:

id ¦ file_name ¦ date_uploaded ¦ folder ¦ int_time
----------------------------------- --------------
1 ¦ name1.gif ¦ 281350Z Jul08 ¦ docs ¦ 0807281350
2 ¦ name1.gif ¦ 281353Z Jul08 ¦ docs ¦ 0807281353
3 ¦ name2.gif ¦ 281350Z Jul08 ¦ docs ¦ 0807281350
4 ¦ name2.gif ¦ 281355Z Jul08 ¦ docs ¦ 0807281355
5 ¦ name2.gif ¦ 281358Z Jul08 ¦ stuff ¦ 0807281358

"SELECT id, file_name, date_uploaded, folder, MAX(int_time) FROM tbl WHERE folder = 'docs' GROUP BY file_name";

Results returned are:

id ¦ file_name ¦ date_uploaded ¦ folder ¦ int_time
--------------------------------------------------
1 ¦ name1.gif ¦ 281350Z Jul08 ¦ docs ¦ 0807281353
3 ¦ name2.gif ¦ 281350Z Jul08 ¦ docs ¦ 0807281355

Note that the correct 'int_time' results are returned. They do not however correspond to the 'date_uploaded' field for the same row. data type of the 'int_time' field is INT(12).

Again, what I would like to have is a single row for each unique file name within each folder, and for said row to be of the latest addition. Such as:

id ¦ file_name ¦ date_uploaded ¦ folder ¦ int_time
----------------------------------- --------------
2 ¦ name1.gif ¦ 281353Z Jul08 ¦ docs ¦ 0807281353
4 ¦ name2.gif ¦ 281355Z Jul08 ¦ docs ¦ 0807281355

select distinct file_name, max( int_time) from tbl WHERE folder = 'docs' group by file_name;

This is horribly nasty, but would get the result:

SELECT file_name, max_time,

(SELECT id FROM tbl WHERE tbl.file_name=maxtimes.file_name AND tbl.int_time=maxtimes.max_time ORDER BY int_time DESC LIMIT 1) AS max_id,

(SELECT date_uploaded FROM tbl WHERE tbl.file_name=maxtimes.file_name AND tbl.int_time=maxtimes.max_time ORDER BY int_time DESC LIMIT 1) AS max_date

FROM

(SELECT file_name, MAX(int_time) as max_time FROM tbl) AS maxtimes

I haven't tested it, so it may need some bug fixes. I wouldn't recommend that though unless its a really small table that's not used a lot.

Instead it might be worth actually extracting the data you need into another summary table on a regular basis.

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

That query returns the following error: "MIXING OF GROUP COLUMNS (MIN(), MAX(), COUNT()...) WITH NO GROUP COLUMNS IS ILLEGAL IF THERE IS NO GROUP BY CLAUSE".

I've tried to debug, but no luck no luck so far.

--------------

@ Demaestro -

Your query returns 0 results. No errors though.

--------------

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.

ID file_name folder int_time
2 name1.gif docs 2008-07-28 13:53:00
4 name2.gif docs 2008-07-28 13:55:00

I neglected to change 'docs' to my correct folder name. I am now getting results, but the final 'GROUP BY t2.file_name' doesn't appear to be taking, as all the rows for the given 'folder' are being returned.

----------

@ Demaestro -

SELECT file_name, max_time,

(SELECT id FROM tbl WHERE tbl.file_name=maxtimes.file_name AND tbl.int_time=maxtimes.max_time ORDER BY int_time DESC LIMIT 1) AS max_id,

(SELECT date_uploaded FROM tbl WHERE tbl.file_name=maxtimes.file_name AND tbl.int_time=maxtimes.max_time ORDER BY int_time DESC LIMIT 1) AS max_date

FROM

(SELECT file_name, MAX(int_time) as max_time FROM tbl GROUP BY file_name) AS maxtimes