Find the max value of a column, then group by another column in same table

Question

I have a similar problem to the one solved here, but when I try the solution I think it fails because I have things set up differently..

I have a doc table with... (unfortunately table cant be edited due to it being an old system)

+-------+--------+----------+--------+
| Docid | title  | revision | linkid |
+-------+--------+----------+--------+
| 1     | docone | 1        | 1      |
| 2     | doctwo | 1        | 2      |
| 3     | docone | 2        | 1      |
|4      | docone | 3        | 1      |
+-------+--------+----------+--------+

On a page that lists all the documents I want to list only the latest revision of each document. Doc1 for example is on revision 3 so I want that one and not the other 2. Doc2 is only on revision 1 so show that one.

Based on the problem in the other post I have writen my query as follows......

$query_docs = "
    SELECT `document`.*, doctype.* 
    FROM `document`   
    INNER JOIN doctype    
        ON `document`.iddoctypes = doctype.iddoctypes
    WHERE `document`.revision = (
        SELECT MAX(`document`.revision) AS revision 
        FROM `document`
    )  
    GROUP BY `document`.linkid   
    ORDER BY `document`.doccreation DESC";

I have had to link to another table to get the document type (just to make the query harder).

You might find this easier if you try to get what you want from the document table first, and then worry about adding in the extra detail from doctype. — nurdglaw, May 30 '13 at 16:11
is that simply reordering the inner join to after the brackets of the sub query? — Daniel Robinson, May 30 '13 at 16:15
Not really; it's more that you're trying to solve a problem to do with getting some data out of the `document` table. I suggest you concentrate on that, and don't worry about adding extra tables for extra data until you've worked out how to get the data you want out of the `document` table. — nurdglaw, May 30 '13 at 16:20

Daniel S. · Answer 1 · 2013-05-30T16:17:35.793

0

Just a wild guess: I think you have to add group by title in your (select max(...) ...) subquery.

So the complete statement will be this:

$query_docs = "
SELECT `document`.*, doctype.* 
FROM `document`   
INNER JOIN doctype    
    ON `document`.iddoctypes = doctype.iddoctypes
WHERE `document`.revision = (
    SELECT MAX(`document`.revision) AS revision 
    FROM `document`
    --GROUP BY `document`.title
    GROUP BY `document`.linkid
)  
GROUP BY `document`.linkid   
ORDER BY `document`.doccreation DESC";

edited May 30 '13 at 16:17

answered May 30 '13 at 16:11

Daniel S.

6,458
4
35
78

Unfortunatly the title could change depending on each revision. The only thing that will always be the same is linkid – Daniel Robinson May 30 '13 at 16:16
Then my hint is to replace "group by title" with "group by linkid". I've edited my post respectively. – Daniel S. May 30 '13 at 16:18

score 0 · Accepted Answer · answered May 30 '13 at 16:15

0

Try this, I made a couple minor changes

SELECT document.*, doctype.* 
FROM document   
INNER JOIN doctype    
    ON document.iddoctypes = doctype.iddoctypes
WHERE document.revision = (
    SELECT MAX(d1.revision)
    FROM document d1 
    WHERE document.linkid = d1.linkid
)  
ORDER BY document.doccreation DESC

answered May 30 '13 at 16:15

Phil Cross

9,017
12
50
84

thank you phil but I think I should of metioned that title can change but linkid can not. – Daniel Robinson May 30 '13 at 16:17
@DanielRobinson I've changed it so that it grabs data based on `linkid` instead of `title`. It should work as expected (I hope!) – Phil Cross May 30 '13 at 16:19
Sorry for the late reply. Thank you very much your solution works. Can I ask something though. I dont understand the line from document d1. Are you changing the name of the doc table to d1? Is there some info out there I can read as to what you have done? – Daniel Robinson Jun 03 '13 at 10:12
Where it says `FROM document d1`, I'm simplying naming the reference to a table. In this instance, whenever I write `d1.revision`, its simply a reference to `document.revision` – Phil Cross Jun 03 '13 at 10:33

score 0 · Answer 3 · answered May 30 '13 at 16:20

0

Am I missing something? This seems completely straightforward...

SELECT x.*
  FROM my_table x
  JOIN (SELECT title,MAX(revision) max_revision FROM my_table GROUP BY title) y
    ON y.title = x.title 
   AND y.max_revision = x.revision;

answered May 30 '13 at 16:20

Strawberry

33,750
13
40
57

Find the max value of a column, then group by another column in same table

3 Answers3