This article helps to get/retrieve the records with latest CONFIRMED and UNCONFIRMED version of the records.

The below query shows the latest unconfirmed records in a particular catalog. Here MCT_34307 is the catalog table.
Select B.* from (SELECT PRODUCTKEYID, MAX(MODVERSION) MODVERSION
FROM PRINCIPALKEY where state = 'UNCONFIRMED' and CATALOGID = '34307'
GROUP BY PRODUCTKEYID) A, MCT_34307 B where A.PRODUCTKEYID = B.CPRODUCTKEYID and A.MODVERSION = B.CMODVERSION;

To see all the latest confirmed records in a particular catalog, we need to query goldencopy table. Here MCT_34307 is the catalog table. Please find below the query.
SELECT mct.*
FROM goldencopy gc, MCT_34307 mct
WHERE (mct.cproductkeyid = gc.productkey and mct.cmodversion = gc.version);