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: 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