Confluence & SQL: How to find largest native documents by character count?

[Seb Kouba]

I got this far but it seems I get two entries if the two biggest documents are simply versions of the same doc:

SELECT content.title, character_length(body) as characters, contenttype
FROM bodycontent, content
WHERE bodycontent.contentid = content.contentid
ORDER BY characters desc
LIMIT 30;

How can I just get the latest version for each page? (I'm using postgresql)

Thanks

[Davin Studer]

In the CONTENT table filter for CONTENT_STATUS = 'current'.

[Seb Kouba]

Unfortunately that doesn't seem to remove the duplicate entries... SELECT content.title, character_length(body) as characters, contenttype FROM bodycontent, content WHERE bodycontent.contentid = content.contentid AND content.content_status='current' ORDER BY characters desc LIMIT 30;