How to get the unique values in a table

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 clauses such as DISTINCT. This clause now works with QueryDef for each of Oracle, SQL Server and Access, as do the MAX, MIN and SUM functions. It is still the case that QueryDef does not support the ORDER BY or GROUP BY clauses or correlated subqueries.
The ISqlSyntax interface provides the means to check if a specific function, clause (i.e., DISTINCT) or predicate is supported by the workspace.
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. The IQueryDef method however, ensures that the work is done on the database server rather than the client.

How to use

  1. Paste the code into your VB or VBA Application.
  2. Call the function from within your application.
[VBA] 
Public 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
    Dim intFieldIndex As Integer
    Set pRow = pCursor.NextRow
    intFieldIndex = pCursor.Fields.FindField(sFieldName)
    Do Until pRow Is Nothing
        Debug.Print pRow.Value(intFieldIndex) ' Note index of desired field
        Set pRow = pCursor.NextRow
    Loop
End Sub