DQL query: Order by Count(*) desc

I'm looking for a dql query which returns the highest count first, can anyone suggest me equivalent DQL queries just like below.

SELECT Group, COUNT(*)
FROM table
GROUP BY Group
ORDER BY COUNT(*) DESC

Find more posts tagged with

Comments

Hicham Bahi

That's the general idea. Did you try it?

If your attribute is really called Group (bad idea!) then you need to enclose it in double-quotes as it is a DQL keyword.

Anshu Kumar

Below is the query which I'm trying to get the result list from dmi_queue_item but I'm getting syntax error:

select message,count(*) from dmi_queue_item where date_sent >= DATE('06/01/2021') and date_sent <= DATE('06/14/2021') and item_type !='spm_transmittal' group by message order by count(*) desc ;

Hicham Bahi

Try order by 2 desc

Anshu Kumar

Yes it working, Thank you 🙂

Anshu Kumar

But using the below query I'm not getting the correct result list, can you suggest what is wrong ?
select message,count(*) from dmi_queue_item group by message ENABLE(RETURN_RANGE 1 10 '2 DESC') ;

Hicham Bahi

Can you be a bit more precise? What results do you get and what is not correct?

Anshu Kumar

Please see the below image file, my expected result list is to get the top 10 highest count first

But on the execution of the mentioned above query, I'm not able to get the ordered list

Hicham Bahi

This might be a bug. I suggest you open a ticket with OpenText support. You also may want to trace the SQL generated by this DQL (e.g. thought Documentum Administrator DQL Editor or sqltrace flag).

Anshu Kumar

Okay I'll open a ticket with OpenText support team. Thanks a lot for your help

Hicham Bahi

And by the way, I just tried something similar and it's working fine for me (using a_content_type and dm_document). I tried it on Documentum Server 16.4.

Anshu Kumar

Thanks a lot for your prompt advice and suggestions, I'll give a try and then I'll update the status over here