Determining the number of Max number of versions

I have always used the following SQL to provide me a list of how many documents contain how many versions but I am now thinking this may be bad SQL.select distinct(version) "NumVersions", count(*) "NumDocsWVer" from dversdata where (version, docid) in (select max(version), docid from dversdata group by docid) group by version;My goal is to list the Number of Versions with the number of documents that contain that many versions. In other words the report would look like:Versions Documents1 1203452 86994...1395 11397 3...6000 1Where the first column contains the number of versions and the second column contains how many documents the system has that have that many versions.My problem is that this SQL, which I have trusted explicitly until recently, told me a client has a document with 10 million versions and when they ran a canned LiveReport they are showing about 6000 as the max number of versions for any single document.I was wondering if anyone has SQL that will show the number of versions (and not the Max Version number since that can be misleading due to the purging of earlier versions) with the number of documents having that number of versions?Thanks or any help.

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Greg,

You are right in that you are ignoring deleted / purged versions when you use the max version that exists.

I had a go without reading your statement too closely.

First off, I wrote a statement to report the count of versions per docid.

Then used that to feed another group by statement.

My combined statement is....

select vercount, count(*) from ( select docid,count(*) as vercount from dversdata group by docid ) group by vercount order by 1 desc

VERCOUNT   COUNT(*)
---------- ----------
        16          1
         8          3
         6          2
         5          1
         4          5
         3          4
         2         16
         1        918

Your statement run on my test system returns the following.

select distinct(version) "NumVersions", count(*) "NumDocsWVer" from dversdata where (version, docid) in
(select max(version), docid from dversdata group by docid) group by version order by 1 desc

numVersions NumDocsWVer
---------- -----------
        24           1
        20           1
        13           1
        11           1
         9           1
         8           2
         7           1
         6           2
         5           1
         4           6
         3          12
         2          33
         1         888

So, in words, your statement returns the count of documents by current version number.

You wanted (I think) the count of documents by number of versions that exist, regardless of what version numbers they might be.

Regards

Lindsay Davies

European Escalation Team

Open Text UK.

From: eLink Discussion: Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2010 December 20, Mon 21:26
To: eLink Recipient
Subject: Determining the number of Max number of versions

Determining the number of Max number of versions

Posted by gkellogg (Kellogg, Gregory) on 2010/12/20 16:25

I have always used the following SQL to provide me a list of how many documents contain how many versions but I am now thinking this may be bad SQL.

select distinct(version) "NumVersions", count(*) "NumDocsWVer" from dversdata where (version, docid) in (select max(version), docid from dversdata group by docid) group by version;

My goal is to list the Number of Versions with the number of documents that contain that many versions. In other words the report would look like:

Versions Documents
1 120345
2 86994
...
1395 1
1397 3
...
6000 1

Where the first column contains the number of versions and the second column contains how many documents the system has that have that many versions.

My problem is that this SQL, which I have trusted explicitly until recently, told me a client has a document with 10 million versions and when they ran a canned LiveReport they are showing about 6000 as the max number of versions for any single document.

I was wondering if anyone has SQL that will show the number of versions (and not the Max Version number since that can be misleading due to the purging of earlier versions) with the number of documents having that number of versions?

Thanks or any help.