DISTINCT

Examines a set, removes duplicate elements, and returns a set of the remaining elements.

Returned Type

This function returns a set.

Syntax and Details

DISTINCT(set_expression)

set_expression is an expression that evaluates to a set.

Example

For example, suppose that the query must return a specific city as reference, which is needed for comparison to the other cities. Consider the following query, which displays a reference city, followed by a set of cities with a given patient count:

WITH SET refcity AS '{homed.juniper}' 
SELECT MEASURES.[%COUNT] ON 0, 
{refcity,FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>1100)} ON 1 FROM patients

                             Patient Count
 1 Juniper                            1,197
 2 Cedar Falls                        1,188
 3 Centerville                        1,155
 4 Cypress                            1,221
 5 Elm Heights                        1,266
 6 Juniper                            1,197
 7 Magnolia                           1,156
 8 Pine                               1,139
 9 Redwood                            1,144
10 Spruce                             1,135

Compare to the following query, which removes the duplicate city:

WITH SET refcity AS '{homed.juniper}' SELECT MEASURES.[%COUNT] ON 0, 
DISTINCT({refcity,FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>1100)}) ON 1 FROM patients
                             Patient Count
1 Juniper                             1,197
2 Cedar Falls                         1,188
3 Centerville                         1,155
4 Cypress                             1,221
5 Elm Heights                         1,266
6 Magnolia                            1,156
7 Pine                                1,139
8 Redwood                             1,144
9 Spruce                              1,135