Get lastest version of a content group SQL

Question

I have content table where I have many contents and various versions of those contents. The columns are content varchar,document_id int (identifies a content between many versions) and version int

I want to return the lastest version of each content group. I tried using this SQL but it returns me just the first version but I want the last.The order by doesn't work with group by.

SELECT * FROM content where id_project = 8 group by document_id order by version desc;

How can i get the lastest version of all unique content(unique content is identified by the document_id)?

Thanks if anyone can help me

do you want the latest content or the latest version number? — Imre L, May 13 '11 at 21:10
@lmre-l I want the lastest content version but your sql seems working fine! thank you! — dextervip, May 13 '11 at 21:25

score 1 · Answer 1 · answered May 13 '11 at 20:59

1

SELECT document_id,MAX(version) FROM content WHERE id_project = 8 GROUP BY document_id

answered May 13 '11 at 20:59

chx

11,270
7
55
129

1

it informs me what is the lastest version but don't bring me the row which has the lastest. – dextervip May 13 '11 at 21:10

Imre L · Accepted Answer · 2011-05-13T21:12:15.230

1

SELECT c1.* FROM content c1
  left join content c2 on c1.document_id = c2.document_id and c1.version < c2.version
 where c2.document_id is null
   and c1.id_project = 8

edited May 13 '11 at 21:12

answered May 13 '11 at 21:04

Imre L

6,159
24
32

bhamby · Answer 3 · 2011-05-13T22:18:07.220

0

I believe you want something like

SELECT *
FROM content A
WHERE version = (
    SELECT MAX(version)
    FROM content B
    WHERE B.document_id = A.document_id
)
AND document_id = 8

Assuming each set of versions is "grouped" by a document_id.

edited May 13 '11 at 22:18

answered May 13 '11 at 22:11

bhamby

15,112
1
45
66

Get lastest version of a content group SQL

3 Answers3