how to get Maxium or Minium value from Salesforce object in pentaho Kettle

We may get funny result if we apply MIN() or MAX() function in Salesforce SOQL query. It may be because the field past into MIN()/MAX() function is a picklist type. "If you use the MIN() or MAX() functions on a picklist field, the function uses the sort order of the picklist values instead of alphabetical order".

So, how can we get Maxium or Minium values through SOQL in kettle ETL process? I have achieve this by using Kettle in memory group by step. Below shows how to step by step.

1.  As discussed in previous post, we can get distinct fields values from Salesforce.
   
2. After getting the result, the trick and simple step is using Javascript step to insert a dummy field with dummy value.
   
3. Now, using Kettle's in memory group by step to get Max or Min value of Attr1__c field from Salesforce object.
   

Implement SELECT DISTINCT in Salesforce SOQL

As far as I know, Salesforce SOQL does not support SELECT DISTINCT. In stead of implementing distinct function in your own application, here is a tip to do SELECT DISTINCT in SOQL.

Suppose we have a Customized salesforce object called Dummy__c, which have attributes Attr1__c, Attr2__c, Attr3__c etc, We want to get distinct values of Attr1__c. To achieve this, we can easily get distinct values of Attr1__c by using GROUP BY with COUNT_DISTINCT function:

SELECT Attr1__c, COUNT_DISTINCT(Attr__c) FROM Dummy__c GROUP BY Attr1__c

That's all. We do not need to load large number of records into our own application's memory and count unique values.