[SOLVED] Selecting a max value among similar rows

[aaronvegh]

I have a MySQL (v. 5.067) table that I'd like to pull a particular value from. Consider this table:

 

mysql> describe repos;

+------------+--------------+------+-----+---------+----------------+

| Field      | Type        | Null | Key | Default | Extra          |

+------------+--------------+------+-----+---------+----------------+

| id        | int(11)      | NO  | PRI | NULL    | auto_increment |

| filename  | varchar(255) | YES  |    | NULL    |                |

| uuid      | varchar(255) | YES  |    | NULL    |                |

| revision  | int(11)      | YES  |    | NULL    |                |

| project_id | int(11)      | YES  |    | NULL    |                |

| message    | varchar(255) | YES  |    | NULL    |                |

| parent_id  | int(11)      | YES  |    | NULL    |                |

| path      | varchar(255) | YES  |    | NULL    |                |

| created_at | datetime    | YES  |    | NULL    |                |

| updated_at | datetime    | YES  |    | NULL    |                |

+------------+--------------+------+-----+---------+----------------+

 

There can be many rows that have an identical "filename" and "path" field. Each time a new file entry is made, the "revision" field will be incremented, so that you'll have rows like this:

 

mysql> select filename, path, revision from repos;

+---------------------------+-------------------------------+

| filename                  | path          | revision |

+---------------------------+---------------------------------------+----------+

| Whitby Chamber Emails.txt | /daytime tv/ |        1 |

| Whitby Chamber Emails.txt | /daytime tv/ |        2 |

| 2361853 EPO inv.pdf      | /daytime tv/ |        1 |

| 2361853 EPO inv.pdf      | /daytime tv/ |        2 |

+---------------------------+-------------------------------------------+

 

Basically, I want a query that would choose the max value for each distinct file. That select statement would produce:

 

+---------------------------+-------------------------------+

| filename                  | path          | revision |

+---------------------------+---------------------------------------+----------+

| Whitby Chamber Emails.txt | /daytime tv/ |        2 |

| 2361853 EPO inv.pdf      | /daytime tv/ |        2 |

+---------------------------+-------------------------------------------+

 

I've been banging my head against the wall on this one for a while now. Any help would be adored!

 

Thanks,

Aaron.

 

 

[kkubek]

I can't test this, but I believe this should do it:

 

select filename, max(path), max(revision)

from repos

group by filename

[aaronvegh]

It works great! Wow, thanks so much, you awesome, awesome SQL nerd! :-D

 

Cheers,

Aaron.

[aaronvegh]

Hmm, actually, this may not be solved. A further investigation showed that this select statement returns not the row with the highest revision number, but instead the earliest rows (those with the lowest revision numbers) with a new field showing the highest revision number. Here's that table from above again, but this time with the id field:

 

mysql> select filename, path, revision, id from repos;

+---------------------------+-------------------------------+

| filename                  | path          | revision | id

+---------------------------+---------------------------------------+----------+

| Whitby Chamber Emails.txt | /daytime tv/ |        1 | 8

| Whitby Chamber Emails.txt | /daytime tv/ |        2 | 9

| 2361853 EPO inv.pdf      | /daytime tv/ |        1 | 10

| 2361853 EPO inv.pdf      | /daytime tv/ |        2 | 11

+---------------------------+-------------------------------------------+

 

So I really want my select statement to return rows 9 and 11. Instead, I get:

 

mysql> select id, filename, max(path), max(revision) from repos group by filename;

+----+---------------------------+-------------------------------------+

| id | filename                  | max(path)        | max(revision) |

+----+---------------------------+-------------------------------------+

| 10 | 2361853 EPO inv.pdf      | /daytime tv/ |            2 |

|  8 | Whitby Chamber Emails.txt | /daytime tv/ |            2 |

+----+---------------------------+-------------------------------------+

 

It's returning the wrong rows with the right revision number. This is a partial solution, since I can then easily take that revision number and find the right row. But is it possible to do this in one query?

 

Thanks!

Aaron.

 

 

[fenway]

MAX(path)???

 

Don't you mean:

 

select filename, path, max(revision) from repos group by filename, path;

[kkubek]

How about:

 

select filename, max(path), max(revision), max(id)

from repos

group by filename

 

And, you might want to consider adding path to the group by (as fenway suggested). It's hard to tell from your data sample if filename is always 1 to 1 to path. Can you have the same filename in 2 diff paths ? If so, add path to the group by. Actually, probably better to do it regardless

[aaronvegh]

that appears to have done the job! Thanks to all for your help.

 

Cheers,

Aaron.