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>
}}-->

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:

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.