UNIQUE_ID
Description
The UNIQUE_ID function returns a unique non-negative INT for each unique tuple in the combination of the input columns.
There is no guarantee which INT will be assigned to which tuple. However, it is guaranteed that the result is always non-negative.
SYNTAX
UNIQUE_ID ( table.column1 , ... , table.columnN )
NULL handling
This operator does not treat NULL values specially. Additionally, the result of UNIQUE_ID can never be NULL.
Examples
[1] UNIQUE_ID can be used to count the distinct number of unique combinations of values from different columns. This example shows a list of six vendors together with the city and country of their headquarters. We want to count the number of different cities our vendors come from. Since just the city names are not unique, it is required to count unique combinations of city name and country. We can use UNIQUE_ID to give a unique ID to each of those combinations, and apply COUNT DISTINCT on these unique IDs: | |||||||||||||||||||||||||||
| |||||||||||||||||||||||||||
|
[2] Same example as above, but the COUNT DISTINCT is not applied. We can see the exact result of the UNIQUE_ID function per vendor. The number that gets assigned to each unique tuple of city and country does not follow a particular pattern, but it is guaranteed that it is non-negative: | |||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||
|