Writing a Query That Returns Distinct Records

Return Distinct Records With LineItemsSOQL

Say you have a LineItemsSOQL table with a query on the Opportunity object that returns a few fields from its parent Account in your template:

<!--{{!
<lineitemsSOQL>
<class>table123</class>
<soql>
SELECT Account.Name, Account.Industry, Account.Type
FROM Opportunity
</soql>
<column>Account.Name</column>
<column>Account.Industry</column>
<column>Account.Type</column>
</lineitemsSOQL>
}}-->

This is fine if you don't mind the fields from a particular Account showing up in several of your table rows (e.g. your query returns 3 Opportunities that are all linked to the same Account). However, what if your use case required that you only display the fields for each particular Account just once in your table, even if your query returns multiple Opportunities linked to that Account? To accomplish this, you would change your LineItemsSOQL code to the following:

<!--{{!
<lineitemsSOQL>
<class>table123</class>
<soql>
SELECT Count_DISTINCT(ID) cid, Account.Name, Account.Industry, Account.Type
FROM Opportunity
GROUP BY Account.Name, Account.Industry, Account.Type
</soql>
<column>Name</column>
<column>Industry</column>
<column>Type</column>
</lineitemsSOQL>
}}-->
Note the following differences:1) COUNT_DISTINCT(Id) cid is included in the query.
2) The fields in the query are repeated after a GROUP BY statement.
3) The column fields are no longer prepended with "Account." This is because this query doesn't return objects corresponding to Opportunity records (which would require traversing up to the Account's field's via "Account.") as it would if COUNT_DISTINCT were not included. Rather, it returns objects corresponding to Account records.

Return Distinct Records With The Named Query Feature

This behavior is also supported with the named query feature. Typically when you query a field through a lookup field (e.g. Contact__r.Name) with the named query feature, you'd prepend the name of the field with the name of the lookup object (e.g. {{!myQuery.Contact__r.Name}}). However, when using named queries with an aggregate function (such as COUNT_DISTINCT), you should omit the name of the lookup object (e.g. use {{!myQuery.Name}} rather than {{!myQuery.Contact__r.Name}} to reference Contact__r.Name) for the same reason listed in (3). For example:

<!--{{!
<lineitemsSOQL>
<class>none</class>
<queryname>myDistinctQuery</queryname>
<soql>
SELECT Count_DISTINCT(ID) cid, Account.Name, Account.Industry, Account.Type
FROM Opportunity
GROUP BY Account.Name, Account.Industry, Account.Type
LIMIT 1
</soql>
</lineitemsSOQL>
}}-->


{{!myDistinctQuery.cid}} <br />
{{!myDistinctQuery.Name}} <br />
{{!myDistinctQuery.Type}} <br />

Return Distinct Records Per Field Value

If you would like your LineItemsSOQL statement to only return one record per field value, you can do so using <distinct> tags.

For example, let's say the following Product records are related to an Opportunity:

Product Product Code Total Price
GenWatt Diesel 1000kW GC1060 $100,000.00
GenWatt Diesel 1000kW GC1060 $100,000.00
Installation: Industrial - High IN7080 $255,000.00
SLA: Bronze SL9020
$20,000.00

Note that GenWatt Diesel 1000kW appears twice. Adding <distinct>productcode</distinct> to your LineItemsSOQL statement will return this record only once, since the product code is the same. In other words, this related list statement:

<style type="text/css">table { border: collapse; }
  table, tr, td, th { border: 1px solid black; }
  th { font-weight: bold; }
</style>
<strong>Unique Products From This Opportunity</strong>
<table cellpadding="4" cellspacing="0">
    <thead>
        <tr>
            <th>Name</th>
            <th>Quantity</th>
            <th>List Price</th>
            <th>Total Price</th>
            <th>Product Code</th>
        </tr>
    </thead>
    <tbody><!--{{!
<LineItemsSOQL>
<class>table151</class>
<listname>opportunitylineitem</listname>
<soql>
SELECT name, quantity, listprice, totalprice, productcode
FROM opportunitylineitem
WHERE opportunityid='{{!Opportunity.id}}'
</soql>
<distinct>productcode</distinct>
<column>name</column>
<column>quantity</column>
<column>listprice</column>
<column>totalprice</column>
<column>productcode</column>
</LineItemsSOQL>
}}-->
    </tbody>
</table>

Would output the following table:

You can also use the sum attribute in your <distinct> tags to sum specified columns in records that are considered duplicates based on your <distinct> field, and roll the results up in each column. For example, you could modify the <distinct> tags from the example above like so:

<distinct sum="quantity,listprice,totalprice">productcode</distinct>

This would output the following table, with the quantity, list price, and total price of the duplicate product summed.

Tags: , ,

Was this helpful?