Reporting out current folder owner

[jm_aussie]

I'm trying to provide a report with all folders in certain stages and want to show who currently is working on the folder (our folders move thru the process and may wait in a queue stage that multiple people can then "pick up" to action).   Since the actions may take days, it's helpful to see which are being actioned by whom without having to open the folder.

I think I need to query the eevent table and get the eusername with the largest eeventid but something's not right.  The query (for just one folder) is

select e.eusername, e.eeventid, e.efolderid
from eevent e,
(select  efolderid,  max(eeventid) as MaxID
 from eevent
 group by efolderid) CurrentDoer
 where e.efolderid = '0900000000000000000000000000773'
 and e.eeventid = CurrentDoer.MaxID

but instead of returning a single user, I still get multiple lines.   Any help would be appreciated.  Thanks!

[Jerome҉]

I think you want the folowing SQL:

select e.eusername, e.eeventid, e.efolderid
from eevent e
WHERE e.efolderid = '0900000000000000000000000000773'
AND eEventId = (select  max(eeventid) from eEvent WHERE efolderid = e.eFolderId )

[BMellert]

Or perhaps (if using SQL Server anyway)

SELECT TOP 1 eUserName

FROM eEvent

WHERE eFolder = '09............0773'

ORDER BY eEventID DESC

[jm_aussie]

Thanks!  That worked.  We're running Oracle so the "Top 1" didn't.