Dynamics Community Forum Thread Details

How can I SELECT rows with MAX(Column value), PARTITION by another column with FetchXml

Hello I have a table that looks like this:

5280.Sans-titre.png

I want select the distinct rows of (numero column) with the max value of the (score column) so for example all the rows with (numero = 13) i want to just get the row with the max score

so my table becomes like this:

Numero Score

12 20

13 50

5 50

I tried this but it didnt work and i get this error (

{"error":{"code":"0x8004111e","message":"An attribute can not be requested when an aggregate operation has been specified and its neither groupby nor aggregate.  NodeXml : <attribute name=\"zs_name\" />"}})


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
 <entity name="zs_reponse">
 <attribute name="zs_name" />
 <attribute name="zs_score" alias='estimatedvalue_max' aggregate='max' />
 <attribute name="zs_reponseid" />
 <order attribute="zs_name" descending="false" />
 <filter type="and">
 <condition attribute="zs_question" operator="not-null" />
 <condition attribute="zs_score" operator="not-null" />
 <condition attribute="zs_parcours" operator="not-null" />
 </filter>
 <link-entity name="zs_question" from="zs_questionid" to="zs_question" visible="false" link-type="outer" alias="a_c876717f515c4c5aa50b931b9b0e6c51">
 <attribute name="zs_coefficient" />
 <attribute name="zs_groupe" />
 <attribute name="zs_intitulefr" />
 <attribute name="zs_numero" distinct="true" />
 <attribute name="zs_parcours" />
 </link-entity>
 </entity>
</fetch>