Get Unique Values

Description:

The code below demonstrates how to find the unique values stored within a database field by using the SQL DISTINCT function.

 

Previous to version 8.1, the QueryDef object did not support SQL group functions such as DISTINCT. This function, as well as MAX, MIN and SUM, now works for QueryDef objects on non-versioned Oracle, SQL Server and Access tables. It is still the case that QueryDef does not support the ORDER BY or GROUP BY clauses or correlated subqueries. The QueryDef still does not support any group functions on versioned tables.

 

An alternative method of finding unique values is to use IDataStatistics (see the example under the IDataStatistics topic), which has the advantage of letting you sample a set of rows. It also works on versioned tables.

How to use:

1. Paste the code into your VB or VBA Application.
2. Call the function from within your application.

Public Sub ShowUniqueValues()Sub ShowUniqueValues(pTable As ITable, sFieldName As String)
  
  Dim pQueryDef As IQueryDef
  Dim pRow As IRow
  Dim pCursor As ICursor
  Dim pFeatureWorkspace As IFeatureWorkspace
  Dim pDataset As IDataset
  
  Set pDataset = pTable
  Set pFeatureWorkspace = pDataset.Workspace
  Set pQueryDef = pFeatureWorkspace.CreateQueryDef
  With pQueryDef
    .Tables = pDataset.Name ' Fully qualified table name
    .SubFields = "DISTINCT(" & sFieldName & ")"
    Set pCursor = .Evaluate
  End With
  
  Set pRow = pCursor.NextRow
  Do Until pRow Is Nothing
    Debug.Print pRow.Value(0) ' Note only one field in the cursor
    Set pRow = pCursor.NextRow
  Loop

End Sub