List all spaces and spacekey and page last modified - ONLY last page modified

[Moses Ebrahimi]

Hi all,

Could someone please help me how to modify below query to be able to list all the spaces with (ONLY) last page that is modified? at the moment the below query list all pages in all spaces with the date they each modified. I want to be able to see the only last page that is been modified.

Confluence server env not cloud and please note macros, plugins etc etc is not an option.

query:

SELECT s.spacekey,

             c.title,

             u.username AS Creator,

             c.creationdate,

             um.username AS LastModifier,

             c.lastmoddate

FROM content c

JOIN spaces s ON c.spaceid = s.spaceid

JOIN user_mapping u ON c.creator = u.user_key

JOIN user_mapping um ON c.lastmodifier = um.user_key

WHERE c.prevver IS NULL

           AND c.contenttype = 'PAGE'

           AND c.content_status = 'current'

Order BY spacekey,title;

Thank you

[Moses Ebrahimi · Accepted solution]

I worked it out and thought to share my findings in case someone else stuck. hope this helps 

SELECT s.spacekey,
             c.title,
             u.username AS Creator,
             c.creationdate,
             um.username AS LastModifier,
             c.lastmoddate
FROM content c
JOIN spaces s ON c.spaceid = s.spaceid
JOIN user_mapping u ON c.creator = u.user_key
JOIN user_mapping um ON c.lastmodifier = um.user_key
WHERE c.prevver IS NULL
AND c.lastmoddate = (select max(c.lastmoddate) from content c where c.spaceid = s.spaceid and c.contenttype = 'PAGE' and c.content_status = 'current')
Order BY spacekey,title;