Home

Data Analysis: Sorting Records

 

 

Ascending Order

If you create a table that includes an identity column and that column is the first (or the most left) column of the table, when the user performs data entry, the records are arranged in the incremental order of the numbers of that identity column. Here is an example:

If you want, you can allow the user to arrange the list of records based on another column. This other column would be used as reference. Rearrange the list of records is referred to as sorting and there are two options: ascending and descending.

When a list is sorted in ascending order:

  • If the list is made or numeric values, the lowest value would become the first, followed by the second to lowest value, and the highest value would become the last. This is the case for the EmployeeID column of the above table
  • If the list is made of strings (words), the alphabet would be used. The string whose first letter is the highest in the alphabet from a, b, c, etc would be the first. In this case, the ascending order is the same as the alphabetical order. For example, in a list made Paul, Walter, Anne, and John, in that order, when sorted in ascending order, the list would become Anne, John, Paul, Walter. If various strings in the list start with the same letter, as is the case for the above FirstName column, the strings with the same starting letter would be grouped first. Then among the strings with the same starting letter, the second letter would be considered and the same algorithm would be applied
  • If the list is made of dates, the earliest date would become the first and the most recent date would become the last
  • If the list is a combination of numbers and strings, the numbers would be arranged in incremental order firs, followed by the list of strings in alphabetical order
  • If the list contains empty values, the empty values would be the first, the other values would be arranged in order depending on their type

All of the techniques used to sort records on a table can also be applied to a query that displays in Datasheet View. To create more advanced queries, the SQL allows you to sort a field on a query and use this arrangement as part of the query.

To sort a column in ascending order, you can include the ORDER BY clause in your statement. The formula used is: 

SELECT What FROM TableName ORDER BY ColumnName;

The What and the TableName factors are the same as described earlier. The ColumnName placeholder allows you to specify the column used as reference. When this operation is performed, the values under the ColumnName column would be sorted in ascending order. The value or the other columns would be used to correspond to those of this column. Consider the following example:

Notice that the records under the LastName column are sorted in alphabetical order, and the other values in the other columns simply follow this order.

If you use the * operator to include all fields, you can order the list based on any of the table's column.

By default, records are ordered in ascending order. Nevertheless, the ascending order is controlled using the ASC keyword specified after the column used as basis. Here is an example:

If you create a database application that contains a table and you want the users to be able to sort values of that table, probably the fastest means of supporting this operation is through the DataGrid control. Its columns are already configured to be sorted in ascending or descending order.

If you are working in a Windows Forms Application and using a DataView object, to sort records, the DataView class is equipped with the Sort property that is of type String. To specify the sort order to apply to the records, assign a formatted string to this property.

Descending Order

Instead of sorting a list in ascending order, you can rearrange its values in reverse order. This is referred to as descending order. The algorithms used are the same as for ascending, except that they are applied in reverse.

If you want to sort records in descending order, use the DESC keyword after the name of the column, in place of ASC. The DESC keyword produces the opposite result to the ASC effect. Here is an example:

Once again, if you are working in a Windows Forms Application and using a DataView object, to sort records, assign an appropriately built string to the DataView.Sort property.
 

Practical Learning Practical Learning: Sorting Records
  1. Design the form as follows:
     
    Control Name Text/CaptionText Other Properties
    Label   Sort:  
    ComboBox cboColumns   DropDownStyle: DropDownList
    Label   in  
    ComboBox cboSortOrder   DropDownStyle: DropDownList
    Label   order  
    DataGrid   Students Records Auto Format: Colorful 2
    Anchor: Top, Bottom, Left, Right
    DataSource: dvwStudents
    Button btnClose Close  
  2. Access the Items property of the right combo box and create two strings as Ascending and Descending 
  3. In the Windows Forms section of the Toolbox, click ContextMenu and click the form
  4. Create the menu items as follows:
     
    Text (Name)
    Filter By Selection mnuFiltBySel
    Filter Excluding Selection mnuFiltExclSel
    -  
    Sort Ascending mnuSortAsc
    Sort Descending mnuSortDesc
    -  
    Remove Filter/Sort mnuRemFiltSort
     
  5. On the form, click the DataGrid control and, in the Properties window, set its ContextMenu property to contextMenu1
  6. Double-click an occupied area of the form to access its Load event
  7. Above the Form1_Load line, declare a DataColumn variable named colSelected 
  8. Implement the Load event of the form as follows:
      
    Dim colSelected As DataColumn

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim tblStudents As DataTable = DsROSH1.Tables("Students")
        Dim colStudents As DataColumnCollection = tblStudents.Columns
        Dim i As Integer

        For i = 0 To colStudents.Count - 1 Step 1
            cboColumns.Items.Add(colStudents(i).ColumnName)
        Next

        cboColumns.SelectedIndex = 0
        cboSortOrder.SelectedIndex = 0

        SqlDataAdapter1.Fill(DsROSH1)

        colSelected = New DataColumn
End Sub
  9. In the Class Name combo box, select dataGrid1
  10. In the Method Name combo box, select MouseDown
  11. Implement the event as follows:
      
    Private Sub dataGrid1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) _
				Handles dataGrid1.MouseDown
        Dim hti As DataGrid.HitTestInfo = dataGrid1.HitTest(e.X, e.Y)
        Dim curCell As DataGridCell

        curCell.RowNumber = hti.Row
        curCell.ColumnNumber = hti.Column

        dataGrid1.CurrentCell = curCell

        colSelected = DsROSH1.Tables("Students").Columns(hti.Column)
End Sub
  12. In the Class Name combo box, select cboColumns
  13. In the Method Name combo box, select SelectedIndexChanged
  14. Implement the event as follows:
      
    Private Sub cboColumns_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles cboColumns.SelectedIndexChanged
        Dim strAscDesc As String = "ASC"

        If cboSortOrder.Text = "Descending" Then
            strAscDesc = "DESC"
        End If

        dvwStudents.Sort = cboColumns.Text & " " & strAscDesc
End Sub
  15. In the Class Name combo box, select cboSortOrder
  16. In the Method Name combo box, select SelectedIndexChanged
  17. Implement the event as follows:
      
    Private Sub cboSortOrder_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) _
				Handles cboSortOrder.SelectedIndexChanged
        Dim strAscDesc As String = "ASC"

        If cboColumns.SelectedIndex = -1 Then
            Exit Sub
        End If

        If cboSortOrder.Text = "Descending" Then
            strAscDesc = "DESC"
        End If

        dvwStudents.Sort = cboColumns.Text & " " & strAscDesc
End Sub
  18. In the Class Name combo box, select mnuSortAsc
  19. In the Method Name combo box, select Click
  20. Implement the event as follows:
      
    Private Sub mnuSortAsc_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles mnuSortAsc.Click
        dvwStudents.Sort = colSelected.ColumnName & " ASC"
End Sub
  21. In the Class Name combo box, select mnuSortDesc
  22. In the Method Name combo box, select Click
  23. Implement the event as follows:
      
    Private Sub mnuSortDesc_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles mnuSortDesc.Click
        dvwStudents.Sort = colSelected.ColumnName & " DESC"
End Sub
  24. In the Class Name combo box, select mnuRemFiltSort
  25. In the Method Name combo box, select Click
  26. Implement the event as follows:
      
    Private Sub mnuRemFiltSort_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
				Handles mnuRemFiltSort.Click
        dvwStudents.RowFilter = Nothing
        dvwStudents.Sort = "StudentID ASC"
End Sub
  27. In the Class Name combo box, select btnClose
  28. In the Method Name combo box, select Click
  29. Implement the event as follows:
      
    Private Sub btnClose_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
			Handles btnClose.Click
        End
End Sub
  30. Execute the application
  31. Test the top combo boxes and test the context menu 
 
Previous Copyright © 2005-2016, FunctionX Next