Sorting tables

Summary This topic shows the workflow for using the ITableSort interface. Use ITableSort to sort a table that has ObjectIDs. It provides the option to sort subsets of a table by specifying selection sets or cursors, and also allows the user to apply a query filter to the sort results. The results can then be accessed via a cursor or an enumerator of ObjectIDs.

In this topic

TableSort class

The TableSort class allows control over the order in which rows are returned from a table or feature class. To use TableSort, an instance of the class is created, properties are set to specify the data and sort options (using the ITableSort interface), and ITableSort.Sort is called to perform the sort. A cursor or a sorted list of ObjectIDs can then be retrieved using the ITableSort.Rows and ITableSort.IDs properties.

Required properties for sorting

The ITableSort interface is used to prepare the sort operation, execute the sort, and retrieve the results. The Fields property and the Table or SelectionSet property must be set, but the rest of the properties are optional and can be used for further refinement of the sorting.
The Fields property is a comma-delimited list of the fields to be sorted. When the sort method is called, the first field is sorted, then the second field, and so on. The Table property specifies the table, object class, or feature class on which the sort is to be performed. The SelectionSet property can be used instead of the Table property, as setting it automatically sets the Table property to the selection set's base table. Not all fields can be included in a sort. For example, by default, a shape field or a raster field cannot be sorted on.
The code examples in this topic show how to sort a feature class containing counties with several census attributes. 
The following code example shows how to instantiate a TableSort object and prepare to sort a feature class:
[C#] 
// Open a feature class of counties.
IFeatureClass featureClass = featureWorkspace.OpenFeatureClass("Counties");
ITable table = (ITable)featureClass;

// Create the TableSort object.
ITableSort tableSort = new TableSortClass();
tableSort.Table = table;

// If the table is the result of a join, remember to fully qualify field names.
tableSort.Fields = "State_Name, Name"; // "Name" is the field for the county name.
[VB.NET] 
' Open a feature class of counties.
Dim featureClass As IFeatureClass = featureWorkspace.OpenFeatureClass("Counties")
Dim table As ITable = CType(featureClass, ITable)

' Create the TableSort object.
Dim tableSort As ITableSort = New TableSortClass()
tableSort.Table = table

' If the table is the result of a join, remember to fully qualify field names.
tableSort.Fields = "State_Name, Name" ' "Name" is the field for the county name.

ITableSort properties

The ITableSort interface has four properties that can be applied alone or in combination to allow the sort to be performed on an existing table or a subset of the data. The following are the five combinations of input that can be used to sort a table (or a subset of the table):

Sort a table

Only set the Table property to sort an entire table. If only a subset of the table needs to be sorted, there are several options.

Sort a table using a cursor

The Cursor property can be used to indicate a subset of the table to be sorted. If the Cursor property is used, set the Table property to the table referenced by the cursor. Setting the Cursor property cancels the SelectionSet property if it has already been set, and causes the sort operation to ignore the QueryFilter property.

Sort a table with a query filter applied

The QueryFilter property can be used to select a subset of records when the sort occurs. If the QueryFilter property is set, the Table property must also be set. Setting the Cursor property causes the QueryFilter property to be ignored. For more information on constructing WHERE clauses, see IQueryFilter.WhereClause.
If the table being sorted is a feature class, a spatial filter (ISpatialFilter) can be used as a query filter, but the feature class's shape field cannot be used in the sort operation.
In the following code example, a query filter is created with a WHERE clause that only returns the counties with a population greater then 10,000:
[C#] 
IQueryFilter queryFilter = new QueryFilterClass();
queryFilter.WhereClause = "POP > 10000";
tableSort.QueryFilter = queryFilter;
[VB.NET] 
Dim queryFilter As IQueryFilter = New QueryFilterClass()
queryFilter.WhereClause = "POP > 10000"
tableSort.QueryFilter = queryFilter
If the IQueryFilter.Subfields property is set, it is ignored as the ITableSort.Fields property always takes precedence.

Sort a selection set

Alternatively, the SelectionSet property can be used to indicate a subset of a table to be sorted. If the SelectionSet property is used, the Table property is set automatically. Setting the SelectionSet property cancels the Cursor property.

Sort a selection set with a query filter applied

The SelectionSet property can be used in conjunction with the QueryFilter property to further refine the subset of data to be sorted.

Refine the sorting parameters

The ITableSort interface has the ability to set specific sorting constraints on a field. The restrictions are set through the following properties:
  • Ascending—Set on a specific field by supplying the field's name. When the Boolean value is set to true for a field, the values in that field are returned in ascending order (for example, 1–9 then A–Z).
  • CaseSensitive—Can only be used on string fields. This property is set to false by default. When set to true, case sensitivity is used as part of the sort algorithm if supported by the workspace.
  • SortCharacters—Can only be used on string fields. This property indicates how many string characters are used to determine the sort order. The default for this property is null, which indicates that all available characters should be used.
The following code example shows how to set these properties:
[C#] 
// Set ascending property with field name and Boolean value.
tableSort.set_Ascending("State_Name", false);
tableSort.set_Ascending("Name", true);
tableSort.set_CaseSensitive("State_Name", true);
tableSort.set_CaseSensitive("Name", true);

// If you only want to use the first five characters for sorting
//tableSort.set_SortCharacters("Name", 5);
[VB.NET] 
' Set ascending property with field name and Boolean value.
tableSort.Ascending("State_Name") = False
tableSort.Ascending("Name") = True
tableSort.CaseSensitive("State_Name") = True
tableSort.CaseSensitive("Name") = True

' If you only want to use the first five characters for sorting
'tableSort.SortCharacters("Name") = 5.
A custom class that implements ITableSortCallBack can be used to apply a user-defined sorting algorithm instead of the default. If implemented, create and assign an instance of the custom class to the Compare property. This property has a default value of null, which indicates the built-in sorting algorithm should be used.

Perform the sort

Once the sorting properties have been set, call the sort method to order the rows. Optionally, the sort method can be provided with an ITrackCancel reference to allow the user to cancel the operation. The typical usage of the sort method is shown in the following code example:
[C#] 
tableSort.Sort(null);
[VB.NET] 
tableSort.Sort(Nothing)

Access the results

After sorting, the Rows property or the IDs property can then be used to access the sorted data. The Rows property returns a cursor, which can be used to iterate through each of the returned rows. The following code example shows the retrieval of results via the Rows property:
[C#] 
// Get a sorted cursor.
ICursor cursor = tableSort.Rows;

// Get field indexes for efficient reuse.
int stateNameIndex = cursor.Fields.FindField("State_Name");
int countyNameIndex = cursor.Fields.FindField("Name");
int popIndex = cursor.Fields.FindField("POP");

// Walk through results and print information about each row.
IRow row = null;
while ((row = cursor.NextRow()) != null)
{
    Console.WriteLine("{0}, {1}, {2}", row.get_Value(stateNameIndex), row.get_Value
        (countyNameIndex), row.get_Value(popIndex));
}
[VB.NET] 
' Get a sorted cursor.
Dim cursor As ICursor = tableSort.Rows

' Get field indexes for efficient reuse.
Dim stateNameIndex As Integer = cursor.Fields.FindField("State_Name")
Dim countyNameIndex As Integer = cursor.Fields.FindField("Name")
Dim popIndex As Integer = cursor.Fields.FindField("POP")

' Walk through results and print information about each row.
Dim row As IRow = cursor.NextRow()

Do While Not row Is Nothing
    Console.WriteLine("{0}, {1}, {2}", row.Value(stateNameIndex), row.Value(countyNameIndex), row.Value(popIndex))
    row = cursor.NextRow()
Loop
The IDs property returns an enumerator containing ObjectIDs. Individual ID values can be returned by their index in the enumerator with the IDByIndex property. The index on the IDs is a zero based index, which means the first object has an index of 0.
The following code example shows the retrieval of the results as an enumerator of IDs and the retrieval of a specific result from the index:
[C#] 
// Get an enumerator of ObjectIDs for the sorted rows.
IEnumIDs enumIDs = tableSort.IDs;

// Get field indexes for efficient reuse.
int stateNameIndex = table.FindField("State_Name");
int countyNameIndex = table.FindField("Name");
int popIndex = table.FindField("POP");

int id =  - 1;
IRow row = null;
while ((id = enumIDs.Next()) !=  - 1)
// -1 is returned after the last valid ID is reached.
{
    row = table.GetRow(id);
    Console.WriteLine("{0} , {1} , {2}", row.get_Value(stateNameIndex),
        row.get_Value(countyNameIndex), row.get_Value(popIndex));
}

// or,

// Get the third feature (for example, what is the third largest county?).
id = tableSort.get_IDByIndex(2); // Zero based index.
row = table.GetRow(id);
Console.WriteLine("{0}, {1}, {2}", row.get_Value(stateNameIndex), row.get_Value
    (countyNameIndex), row.get_Value(popIndex));
[VB.NET] 
' Get an enumerator of ObjectIDs for the sorted rows.
Dim enumIDs As IEnumIDs = tableSort.IDs

' Get field indexes for efficient reuse.
Dim stateNameIndex As Integer = table.FindField("State_Name")
Dim countyNameIndex As Integer = table.FindField("Name")
Dim popIndex As Integer = table.FindField("POP")
Dim id As Integer = enumIDs.Next()
Dim row As IRow = Nothing

Do While id <> -1 ' -1 is returned after the last valid ID is reached.
    row = table.GetRow(id)
    Console.WriteLine("{0} , {1} , {2}", row.Value(stateNameIndex), row.Value(countyNameIndex), row.Value(popIndex))
    id = enumIDs.Next()
Loop

' or,

' Get the third feature (for example, what is the third largest county?).
id = tableSort.IDByIndex(2) ' Zero based index.
row = table.GetRow(id)
Console.WriteLine("{0}, {1}, {2}", row.Value(stateNameIndex), row.Value(countyNameIndex), row.Value(popIndex))

Implement a custom comparison method

In most cases, the default TableSort comparison methods, with the ability to sort in ascending or descending order, provides developers with the necessary output. There are certain cases where a custom comparison method is necessary, such as when a combination of letters and numbers are stored together in a string field. One example of this is a feature class of buildings with street numbers that optionally have letters as suffixes. The following illustration shows the results of an out-of-the-box sort in this situation:
 
The following includes other scenarios where a custom comparison method is beneficial:
  • Sorting based on a single component of values, that is, sorting based on the time of day (but ignoring the date) of a DateTime field.
  • Sorting based on the result of a function on values, that is, the length of strings in a field.
In most cases, the need for a custom comparison method can be avoided by changing how the data is modeled. For example, rather than implementing a method that compares street numbers with optional suffixes, the street numbers could be stored in an integer field with the suffixes in a separate string field. Custom comparison methods should primarily be used in cases where schema modification is not an option, as they can impact the performance of the sort operation.
A custom comparison method can be implemented by creating a class that implements the ITableSortCallBack interface. This interface defines a single method (Compare) that takes two objects as parameters and returns an integer indicating the relationship between the two objects. The four valid return values are –1, 0, 1, and 2. The following table shows each value and description:
Value
Description
–1
First value is less than the second value.
0
Values are equal.
1
First value is greater than the second value.
2
Use the default comparison method.
Compare has two additional parameters that represent the indexes of the field being compared. The fieldIndex parameter indicates the position of the field in the dataset's fields collection, and the fieldSortIndex parameter indicates the position of the field in the TableSort's fields list. Most implementations of Compare do not require this information.
Once a class has been created, a TableSort can be instructed to use it by setting its Compare property to an instance of the class. See the following code example:
[C#] 
ITableSort tableSort = new TableSortClass();
tableSort.Compare = new StreetNumberSort();
tableSort.Table = table;
tableSort.Fields = "StreetNum";
[VB.NET] 
Dim tableSort As ITableSort = New TableSort
tableSort.Compare = New StreetNumberSort
tableSort.Table = table
tableSort.Fields = "StreetNum"
The following code example shows how to create a class with a custom comparison method to use for street numbers with optional letters as suffixes:
[C#] 
public class StreetNumberSort: ITableSortCallBack
{
    public int Compare(object value1, object value2, int fieldIndex, int
        fieldSortIndex)
    {
        // Seperate the numeric and non-numeric components of each value.
        int value1Number =  - 1;
        int value2Number =  - 1;
        String value1Suffix = null;
        String value2Suffix = null;
        SeperateValues(value1.ToString(), out value1Number, out value1Suffix);
        SeperateValues(value2.ToString(), out value2Number, out value2Suffix);

        // Compare the numeric components of the street numbers.
        if (value1Number != value2Number)
        {
            // If value1 is less than value2, return -1; otherwise, return 1.
            if (value1Number < value2Number)
            {
                return  - 1;
            }
            else
            {
                return 1;
            }
        }

        // The numeric values are equal. Compare the suffixes.
        int compareResult = String.Compare(value1Suffix, value2Suffix);
        if (compareResult < 0)
        {
            return  - 1;
        }
        else if (compareResult == 0)
        {
            return 0;
        }
        else
        {
            return 1;
        }
    }

    private void SeperateValues(String streetNumber, out int number, out String
        suffix)
    {
        // Step through the street number to the end or until a null character is reached.
        StringBuilder numberBuilder = new StringBuilder();
        StringBuilder suffixBuilder = new StringBuilder();
        for (int i = 0; i < streetNumber.Length && streetNumber[i] != '\0'; i++)
        {
            Char currentChar = streetNumber[i];
            if (currentChar >= '0' && currentChar <= '9')
            {
                numberBuilder.Append(currentChar);
            }
            else
            {
                suffixBuilder.Append(currentChar);
            }
        }

        // Set the outbound parameters.
        Int32.TryParse(numberBuilder.ToString(), out number);
        suffix = suffixBuilder.ToString();
    }
}
[VB.NET] 
Public Class StreetNumberSort
    Implements ITableSortCallBack
    
    Public Function Compare(ByVal value1 As Object, ByVal value2 As Object, _
                            ByVal fieldIndex As Integer, ByVal fieldSortIndex As Integer) As Integer _
                            Implements ITableSortCallBack.Compare
        
        ' Seperate the numeric and non-numeric components of each value.
        Dim value1Number As Integer = -1
        Dim value2Number As Integer = -1
        Dim value1Suffix As String = Nothing
        Dim value2Suffix As String = Nothing
        SeperateValues(value1.ToString(), value1Number, value1Suffix)
        SeperateValues(value2.ToString(), value2Number, value2Suffix)
        
        ' Compare the numeric components of the street numbers.
        If value1Number <> value2Number Then
            ' If value1 is less than value2, return -1; otherwise, return 1.
            If value1Number < value2Number Then
                Return -1
            Else
                Return 1
            End If
        End If
        
        ' The numeric values are equal. Compare the suffixes.
        Dim compareResult As Integer = String.Compare(value1Suffix, value2Suffix)
        If compareResult < 0 Then
            Return -1
        ElseIf compareResult = 0 Then
            Return 0
        Else
            Return 1
        End If
    End Function
    
    Private Sub SeperateValues(ByVal streetNumber As String, ByRef Number As Integer, ByRef suffix As String)
        ' Step through the street number to the end or until a null character is reached.
        Dim numberBuilder As StringBuilder = New StringBuilder
        Dim suffixBuilder As StringBuilder = New StringBuilder
        Dim i As Integer = 0
        While i < streetNumber.Length And Not streetNumber(i) = Nothing
            Dim currentChar As Char = streetNumber(i)
            If currentChar >= "0" And currentChar <= "9" Then
                numberBuilder.Append(currentChar)
            Else
                suffixBuilder.Append(currentChar)
            End If
            i = i + 1
        End While
        
        ' Set the outbound parameters.
        Int32.TryParse(numberBuilder.ToString(), Number)
        suffix = suffixBuilder.ToString()
    End Sub

End Class

Understand the workflow to sort tables and selection sets

The ITableSort interface can only be used to sort tables that have an ObjectID field. The Fields and Table property, or the SelectionSet property are required. It is possible to only sort a subset of the table. Specific field level sorting options can be used to determine the order the rows are returned. It is possible to use a custom sort algorithm.
In conclusion, the following code example shows two complete functions that can be used to understand the overall workflow of sorting a table and a selection set:
[C#] 
// Expected result. Counties sorted by descending state and ascending county name.
public void SortCursor(ITable table, ICursor cursor)
{
    // Construct the sort and set required fields.
    ITableSort tableSort = new TableSortClass();
    tableSort.Table = table;
    tableSort.Fields = "State_Name, Name";

    // Set the cursor.
    tableSort.Cursor = cursor;

    // Refine the sorting parameters.
    tableSort.set_Ascending("State_Name", false);
    tableSort.set_Ascending("Name", true);
    tableSort.set_CaseSensitive("State_Name", true);
    tableSort.set_CaseSensitive("Name", true);

    // Perform the sort and get results as a cursor.
    tableSort.Sort(null);
    ICursor resultCursor = tableSort.Rows;

    // Display the results.
    int stateNameIndex = resultCursor.Fields.FindField("State_Name");
    int countyNameIndex = resultCursor.Fields.FindField("Name");
    int popIndex = resultCursor.Fields.FindField("POP");
    IRow row = null;
    while ((row = resultCursor.NextRow()) != null)
    {
        Console.WriteLine("{0}, {1}, {2}", row.get_Value(stateNameIndex),
            row.get_Value(countyNameIndex), row.get_Value(popIndex));
    }
}
[VB.NET] 
' Expected result. Counties sorted by descending state and ascending county name.

Public Sub SortCursor(ByVal table As ITable, ByVal cursor As ICursor)
    
    ' Construct the sort and set required fields.
    Dim tableSort As ITableSort = New TableSortClass()
    tableSort.Table = table
    tableSort.Fields = "State_Name, Name"
    
    ' Set the cursor.
    tableSort.Cursor = cursor
    
    ' Refine the sorting parameters.
    tableSort.Ascending("State_Name") = False
    tableSort.Ascending("Name") = True
    tableSort.CaseSensitive("State_Name") = True
    tableSort.CaseSensitive("Name") = True
    
    ' Perform the sort and get results as a cursor.
    tableSort.Sort(Nothing)
    Dim resultCursor As ICursor = tableSort.Rows
    
    ' Display the results.
    Dim stateNameIndex As Integer = resultCursor.Fields.FindField("State_Name")
    Dim countyNameIndex As Integer = resultCursor.Fields.FindField("Name")
    Dim popIndex As Integer = resultCursor.Fields.FindField("POP")
    Dim row As IRow = Nothing
    
    Do While Not row Is Nothing
        Console.WriteLine("{0}, {1}, {2}", row.Value(stateNameIndex), row.Value(countyNameIndex), row.Value(popIndex))
        row = resultCursor.NextRow()
    Loop
    
End Sub
[C#] 
// Expected result. Return the record for the third largest county
// or get a list of ordered IDs largest to smallest population.
public void SortSelection(ITable table, ISelectionSet selSet)
{
    // Construct the sort and set required fields.
    ITableSort tableSort = new TableSortClass();
    tableSort.Fields = "POP";
    tableSort.SelectionSet = selSet;

    // Refine the sorting parameters, sort, and get the ID enumeration.
    tableSort.set_Ascending("POP", false);
    tableSort.Sort(null);
    IEnumIDs enumIDs = tableSort.IDs;

    // Get the third feature (for example, what is the third largest county?).
    int id = tableSort.get_IDByIndex(2); //Zero based index.
    IRow row = table.GetRow(id);

    // Get field indexes for efficient reuse.
    int stateNameIndex = row.Fields.FindField("State_Name");
    int countyNameIndex = row.Fields.FindField("Name");
    int popIndex = row.Fields.FindField("POP");

    // Display the results.
    Console.WriteLine("{0}, {1}, {2}", row.get_Value(stateNameIndex), row.get_Value
        (countyNameIndex), row.get_Value(popIndex));

    // Or iterate through all of the results...
    while ((id = enumIDs.Next()) !=  - 1)
    // -1 is returned after the last valid ID has been reached.
    {
        row = table.GetRow(id);
        Console.WriteLine("{0}, {1}, {2}", row.get_Value(stateNameIndex),
            row.get_Value(countyNameIndex), row.get_Value(popIndex));
    }
}
[VB.NET] 
' Expected result: Return the record for the third largest county
' or get a list of ordered IDs largest to smallest population.

Public Sub SortSelection(ByVal table As ITable, ByVal selSet As ISelectionSet)
    
    ' Construct the sort and set required fields.
    Dim tableSort As ITableSort = New TableSortClass()
    tableSort.Fields = "POP"
    tableSort.SelectionSet = selSet
    
    ' Refine the sorting parameters, sort, and get the ID enumeration.
    tableSort.Ascending("POP") = False
    tableSort.Sort(Nothing)
    Dim enumIDs As IEnumIDs = tableSort.IDs
    
    ' Get the third feature (for example, what is the third largest county?).
    Dim id As Integer = tableSort.IDByIndex(2) 'Zero based index.
    Dim row As IRow = table.GetRow(id)
    
    ' Get field indexes for efficient reuse.
    Dim stateNameIndex As Integer = row.Fields.FindField("State_Name")
    Dim countyNameIndex As Integer = row.Fields.FindField("Name")
    Dim popIndex As Integer = row.Fields.FindField("POP")
    
    ' Display the results.
    Console.WriteLine("{0}, {1}, {2}", row.Value(stateNameIndex), row.Value(countyNameIndex), row.Value(popIndex))
    
    ' Or iterate through all of the results...
    Do While id <> -1 ' -1 is returned after the last valid ID has been reached.
        row = table.GetRow(id)
        Console.WriteLine("{0}, {1}, {2}", row.Value(stateNameIndex), row.Value(countyNameIndex), row.Value(popIndex))
        id = enumIDs.Next()
    Loop
    
End Sub





To use the code in this topic, reference the following assemblies in your Visual Studio project. In the code files, you will need using (C#) or Imports (VB .NET) directives for the corresponding namespaces (given in parenthesis below if different from the assembly name):
Development licensing Deployment licensing
ArcView ArcView
ArcEditor ArcEditor
ArcInfo ArcEditor
Engine Developer Kit Engine Runtime