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

Question

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

Seb Kouba · Answer

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;

Davin Studer · Answer

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