Distinct values from an IDO

Hi, you need to join the table coitem back on itself, joining on the distinct columns and only return the record with the lowest, or highest, primary key value. Assuming you have a primary key (unique field) on the coitem table and lets assume its itemid what you are trying to achieve is this:

SELECT a.co_num, a.due_date
FROM coitem a
LEFT JOIN coitem b ON a.co_num = b.co_num AND a.due_date = b.due_date AND a.itemid > b.itemid
WHERE b.itemid is Null

Create an IDO extension to the IDO for coitem.
Add a second instance of the coitem table to the IDO with an alias different to the original table, in the example I have used a and b
In the join expression, join the fields you want to be distinct and also where the primary key in the original table is greater then (or less than works too) the second instance
When you call the IDO, you will need to add a where clause that the joined table does not exist, i.e. where the primary key is least (or greatest)

IDO's support the "Distinct" keyword, but I can't find anything on how its implemented (ie: which column/properties are you considering "distinct"). Depending on how that functions it should work for you.

Wow... I am amazed...  This is a wonderful SQL Script. I really liked the logic of self-join and then getting the largest or smallest.

Thanks Richards, I am grateful.

For benefit of others, here is the working query.

SELECT a.co_num, a.due_date
FROM coitem a
LEFT JOIN coitem b ON a.co_num = b.co_num AND a.due_date = b.due_date AND a.co_line > b.co_line
WHERE b.co_line is Null 

I was also thinking that somewhere I have used distinct in the past. Then I recalled that in IDO Rest API Web Services,, in LoadCollection we can use distinct. But not in IDOs directly.