mysql: need help with query

BrianK · 07-08-2008, 08:38 PM

A bit of the db in question looks like so:

Code:

mysql> SELECT dailies_id,dailies_shot_name,dailies_department, dailies_ctime FROM dailies WHERE dailies_project_id = '1110' ORDER BY dailies_ctime DESC LIMIT 20;
+------------+-------------------+--------------------+---------------------+
| dailies_id | dailies_shot_name | dailies_department | dailies_ctime       |
+------------+-------------------+--------------------+---------------------+
|       1655 | tb146_355         | effects            | 2008-07-08 18:15:40 |
|       1652 | tb146_325         | lighting           | 2008-07-08 16:50:03 |
|       1648 | tb146_355         | lighting           | 2008-07-08 11:20:58 |
|       1647 | tb146_355         | effects            | 2008-07-08 10:52:01 |
|       1646 | tb146_355         | effects            | 2008-07-08 10:46:27 |
|       1644 | tb146_300         | lighting           | 2008-07-07 15:08:31 |
|       1643 | ao150_005         | layout             | 2008-07-07 13:56:21 |
|       1642 | tb146_325         | lighting           | 2008-07-07 12:59:44 |
|       1641 | tb145_110         | lighting           | 2008-07-07 12:16:15 |
|       1640 | tb146_405         | lighting           | 2008-07-07 12:09:51 |
|       1639 | tb145_110         | lighting           | 2008-07-07 12:06:11 |
|       1638 | ao150_005         | lighting           | 2008-07-07 11:54:19 |
|       1637 | tb146_405         | lighting           | 2008-07-07 10:14:55 |
|       1636 | tb146_355         | effects            | 2008-07-07 10:11:00 |
|       1635 | tb146_325         | lighting           | 2008-07-03 19:20:34 |
|       1634 | tb146_230         | effects            | 2008-07-03 18:13:50 |
|       1633 | tb146_355         | effects            | 2008-07-03 17:55:14 |
|       1632 | tb146_355         | effects            | 2008-07-03 17:37:19 |
|       1631 | tb146_355         | effects            | 2008-07-03 17:25:31 |
|       1630 | tb146_405         | lighting           | 2008-07-03 16:50:31 |
+------------+-------------------+--------------------+---------------------+

Note the colored lines.

My question:
when dailies_shot_name is the same, how can I return only the most recent (determined by the dailies_ctime field) entry?

In other words, if there were no LIMIT on the above query, I would get about 1500 entries, but there are only about 150 distinct shot_name entries. How can I return only the most recent, distinct shot name while also returning the other fields?

j-ray · 07-09-2008, 02:06 AM

what mysql version? you need an inner join probably
http://dev.mysql.com/doc/refman/5.0/en/select.html

BrianK · 07-09-2008, 11:11 AM

Quote:

Originally Posted by j-ray

what mysql version? you need an inner join probably
http://dev.mysql.com/doc/refman/5.0/en/select.html

Version 5.0.

Aren't joins for linking multiple tables together? All of this info is in one table. Can you join one table with itself? If so, will that help with what I'm after? I'm not seeing it in the docs.

excel28 · 07-09-2008, 11:48 AM

Have you tried a 'group by' dailies_shot_name and 'having max(dailies_ctime)' in your query? This is from memory, but I think that should do. But the basic idea is to group the results, then get the largest item in the group.

BrianK · 07-09-2008, 08:07 PM

scratch that, this still isn't working.

I was trying to do this:

Code:

        SELECT dailies_shot_name,dailies_id,MAX(dailies_ctime)
        FROM dailies
        WHERE dailies_project_id = '%d'
        GROUP BY dailies_shot_name;

while it was giving back the correct shot_name and ctime, it was giving the incorrect id... the same id that would be returned if MAX(dailies_ctime) was not used.

BrianK · 07-09-2008, 08:53 PM

So I've come up with a solution, but it's pretty inefficient. It takes 4 seconds to calculate on a table with about 1700 columns, returning about 150 columns. If anyone knows how to optimize, I'd love to see it. Here's what works:

Code:

SELECT dailies_id,dailies_shot_name,dailies_department,dailies_ctime 
FROM dailies d1 
WHERE d1.dailies_project_id='1110' 
AND d1.dailies_ctime = (SELECT MAX(d2.dailies_ctime) FROM dailies d2 WHERE d1.dailies_shot_name = d2.dailies_shot_name);

Per the docs, I also tried this:

Code:

SELECT d1.dailies_id,d1.dailies_shot_name,d1.dailies_department,d1.dailies_ctime 
 FROM dailies d1 
 LEFT JOIN dailies d2 
  ON d1.dailies_shot_name = d2.dailies_shot_name 
  AND d1.dailies_ctime < d2.dailies_ctime 
 WHERE d2.dailies_ctime IS NULL 
  AND d1.dailies_project_id='1110';

with identical results re: time.

chrism01 · 07-09-2008, 09:06 PM

Just for anyone else reading this, I think you mean 'rows', not 'columns' (I hope)

But for that kind of requirement, nested sub-select is normal. 4 secs isn't too bad. Maybe you need better indexes if you've got very large tables?
I've actually found that sometimes its quicker overall to wrap 2 separate simple selects in eg Perl and retrieve the wanted rows from the Perl data structures.

BrianK · 07-09-2008, 09:10 PM

Solved. Thanks to the docs and a little finagling, this does the same as above, but in 0.06 seconds rather than ~4 seconds.

Code:

SELECT d1.dailies_id,d1.dailies_shot_name,d1.dailies_department,d1.dailies_ctime 
FROM dailies d1, (SELECT dailies_shot_name, MAX(dailies_ctime) AS dailies_ctime FROM dailies GROUP BY dailies_shot_name) as d2
WHERE d1.dailies_project_id ='1110' 
 AND d1.dailies_shot_name = d2.dailies_shot_name 
 AND d1.dailies_ctime = d2.dailies_ctime;

The magic being the subquery that QUICKLY produces a very nice table that can easily be used for comparison.

BrianK · 07-09-2008, 09:10 PM

Quote:

Originally Posted by chrism01

Just for anyone else reading this, I think you mean 'rows', not 'columns' (I hope)

hehe... yes, rows. (smacks forehead)

edited to add: In case anyone skips to the bottom, this problem was solved in Post #8.