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
Copy code to clipboard
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
Copy code to clipboard