DQL to return top distinct values

I would like to return a list of distinct values sorted by a property other than the returned property.

For example, to return a distinct list of the 10 users that have most recently updated documents. The following DQL describes what I am looking for - however it is not valid because the ORDER BY items must appear in the SELECT list when SELECT DISTINCT is used:

SELECT DISTINCT r_modifier FROM dm_document ORDER BY r_modify_date DESC ENABLE(RETURN_TOP 10)

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

PanfilovAB

select r_modifier, max(r_modify_date)

from dm_document

group by r_modifier

order by 2 desc

enable(RETURN_TOP 10)

All comments

christopher.imes

Please consider moving this question as-is (no need to recreate) to the proper forum for maximum visibility. Questions written to the users' own "Discussions" space don't get the same amount of attention and can go unanswered for a long time.

You can do so by selecting "Move" under ACTIONS along the upper-right. Then search for: "Documentum" and select the first result that appears. This will relocate it to the Developer Network space of the Documentum community.

Documentum

For further guidance on engaging with the communities for Documentum and related products, please refer to the

IIG Communities Getting Started Guide.

PanfilovAB

select r_modifier, max(r_modify_date)

from dm_document

group by r_modifier

order by 2 desc

enable(RETURN_TOP 10)

PanfilovAB

or:

select r_modifier from

(select r_modifier, max(r_modify_date) as r_modify_date

from dm_document

group by r_modifier)

order by r_modify_date desc

enable(RETURN_TOP 10)