05/15/2023 03:47 AM
Hi experts,
We are creating a report that should fetch count of most frequently requested entitlements in Desceding order, we are able to fetch the entitlements, but even if we are using order by function count is not getting sorted by count automatically.
SELECT
distinct count(distinct (substring_index(ar.JBPMPROCESSINSTANCEID,'.',-1))) as 'count',
ev.Displayname AS 'Entitlement Value',e.displayname as 'Endpoint' FROM ars_requests ar, request_access ra, request_access_attrs ras, entitlement_values ev, endpoints e where ar.requestkey=ra.requestkey and ra.request_accesskey=ras.request_access_key and ar.requesttype='1'and ras.attribute_name='ENDPOINT' and ev.entitlement_Valuekey= ra.ACCESSKEY and e.endpointkey= ras.attribute_value and e.endpointname not in ('saviynt','Organizations','Datasets') group by ev.Displayname ORDER BY count(ev.Displayname) DESC LIMIT 20;
Could you please help me here, if any way to achieve this?
Thanks
Siva Avula
05/15/2023 07:21 AM
Hello @Siva,
You may try the below query to achieve your use case,
SELECT distinct count(distinct (substring_index(ar.JBPMPROCESSINSTANCEID,'.',-1))) as 'count',
ev.Displayname AS 'Entitlement Value',e.displayname as 'Endpoint' FROM ars_requests ar, request_access ra, request_access_attrs ras, entitlement_values ev, endpoints e where ar.requestkey=ra.requestkey and ra.request_accesskey=ras.request_access_key and ar.requesttype='1'and ras.attribute_name='ENDPOINT' and ev.entitlement_Valuekey= ra.ACCESSKEY and e.endpointkey= ras.attribute_value and e.endpointname not in ('saviynt','Organizations','Datasets') group by ev.Displayname ORDER BY count DESC LIMIT 20;
Thanks,
05/15/2023 07:40 AM
Hello @sudeshjaiswal
Thanks for the query, Still the count is coming in Ascending order only.
Is there any limitation from saviynt side like first coulmn in the analytic report is automatically arranged in ascending order?
Thanks
Siva Avula
05/15/2023 02:09 PM
Hi @Siva
I think in analytics , by default it sorted into ascending order based on first column in your query.
If first column is count then the output will be sorted into ascending order based on count.
If first column is application name then the output will be sorted into ascending order based on application name.
Hope this helps!!
Thanks
05/16/2023 04:42 AM
Hi @Siva
If you are using the query in Analytics the first column is always taken into consideration and order by is used and displayed in ascending order. So please construct your query accordingly.
Thanks
Darshan
05/16/2023 08:06 PM
Hi @Darshanjain
Thanks the info,
Can you please help me , What if, If I want to make any column in Descending order in analytic query?How can i do that?
Is it doable?
Thanks
Siva Avula
05/16/2023 09:20 PM
SELECT * FROM emp_salary ORDER BY age ASC, salary DESC