Making Distinct Work

For those of you who have migrated from writing Classic CCL, you probably can't live without the "Select Distinct" approach we've all used. For those of you who are using CCL for the first time in Millennium, consider the "Select Distinct" feature as one more tool in your bag.

"Select Distinct" allows you to suppress duplicate rows of data from appearing in your output. It is dependent on and controlled by the "Order" clause in your code. The fields you list in the "Order" clause are used to determine when a row of data is unique or duplicated.

And that's all you used to need to know in Classic. In Millennium, however, you need to add a zero (0) as the last element in the "Order" list.

Here's a code snippet to demonstrate:

Select Distinct
alias.field1,
alias.field2,
etc.
From
Table Alias
Where (whatever your qualifications are)
Order
Alias.Field1,
Alias.Field2,
0
With
Counter
End
Go

So, what happens if you don't include the "0" in the "Order" clause? Distinct is then based on the uniqueness of the elements in the "Select" clause.

For example,