Distinct Results from SmartObject

Depending on how you execute a SmartObject, as an example if the SmartObject is executed via the ADO provider you can construct a Select statement that makes use of Distinct. You can test this using the  SmartObject Service Tester. Locate the SmartObject in the service tester, right click the SO and select ADO Query. Below an example of the types of queries you can use: 

The following example uses DISTINCT to prevent the retrieval of duplicate titles.

SELECT DISTINCT Name
FROM CT_Region p
WHERE EXISTS
(SELECT *
FROM ct_employee pm
WHERE pm.ct_regionname = p.name
AND pm.id = 2)

The following example shows queries that are semantically equivalent and illustrates the difference between using the EXISTS keyword and the IN keyword.

SELECT DISTINCT Name
FROM CT_Region p
WHERE EXISTS
(SELECT *
FROM ct_employee pm
WHERE pm.ct_regionname = p.name
AND pm.id = 2)

 -- OR

SELECT DISTINCT Name
FROM CT_Region
WHERE Name IN
(SELECT ct_regionname
FROM ct_employee pm
WHERE pm.id = 2)

The following example uses IN in a correlated, or repeating, subquery. This is a query that depends on the outer query for its values. The query is executed repeatedly, one time for each row that may be selected by the outer query.

SELECT DISTINCT c.name, c.id
FROM ct_employee c JOIN ct_region e
ON c. ct_regionname = e.name WHERE 1 IN
(SELECT ID
FROM ct_employee sp
WHERE sp. ct_regionname = e.name)

 For more info please visit the following help topic: http://help.k2.com/en/k2blackpointdevref1420.aspx?page=sample_direct_execution_queries.html this is related to K2 blackpoint but works the same in K2 blackpearl.

Yes! have a look at the following article in our documentation: http://help.k2.com/helppages/k2blackpearlDevRef4.6.6/webframe.html?ado_provider_best_practices.html

Let me know if you have further questions.