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 of 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 first, 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. In the class, declare a DataColumn variable named colSelected
      
    private:

		/// <summary>

		/// Required designer variable.

		/// </summary>

		System::ComponentModel::Container * components;

			 DataColumn *colSelected;
  8. Implement the Load event of the form as follows:
      
    System::Void Form1_Load(System::Object *  sender, System::EventArgs *  e)

{

	 DataTable *tblStudents = this->dsROSH1->Tables->Item[S"Students"];

	 DataColumnCollection *colStudents = tblStudents->Columns;



	 for(int i = 0; i < colStudents->Count; i++)

		 this->cboColumns->Items->Add(colStudents->Item[i]->ColumnName);



	 this->cboColumns->SelectedIndex = 0;

	 this->cboSortOrder->SelectedIndex = 0;



	 this->sqlDataAdapter1->Fill(this->dsROSH1);

			 

	 colSelected = new DataColumn;

}
  9. Return to the form and click the DataGrid control. In the Events section of the Properties window, generate the MouseDown event and implement it as follows:
      
    System::Void dataGrid1_MouseDown(System::Object *  sender,

				System::Windows::Forms::MouseEventArgs *  e)

{

	 DataGrid::HitTestInfo *hti = this->dataGrid1->HitTest(e->X, e->Y);

	 DataGridCell curCell;



	 curCell.RowNumber = hti->Row;

	 curCell.ColumnNumber = hti->Column;



	 this->dataGrid1->CurrentCell = curCell;

			 

	 colSelected = this->dsROSH1->Tables->Item[S"Students"]->Columns->Item[hti->Column];

}
  10. Return to the form. Double-click the combo box on the right side of the Sort label and implement its SelectedIndexChanged event as follows:
      
    System::Void cboColumns_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)

{

	 String *strAscDesc = S"ASC";



	 if( this->cboSortOrder->Text->Equals(S"Descending") )

		 strAscDesc = S"DESC";



	 this->dvwStudents->Sort = String::Concat(this->cboColumns->Text, S" ", strAscDesc);

}
  11. Return to the form and double-click the combo box on the right side of the in label
  12. As done for the first combo boc, implement the SelectedIndexChanged event as follows:
      
    System::Void cboSortOrder_SelectedIndexChanged(System::Object *  sender, System::EventArgs *  e)

{

	 String *strAscDesc = S"ASC";



	 if( this->cboColumns->SelectedIndex == -1 )

		 return;



	 if( this->cboSortOrder->Text->Equals(S"Descending") )

		 strAscDesc = S"DESC";



	 this->dvwStudents->Sort = String::Concat(this->cboColumns->Text, S" ", strAscDesc);

}
  13. Return to the form and click contextMenu1
  14. In the menu, double-click Sort Ascending and implement its event as follows:
      
    System::Void mnuSortAsc_Click(System::Object *  sender, System::EventArgs *  e)

{

	 this->dvwStudents->Sort = String::Concat(colSelected->ColumnName, S" ASC");

}
  15. Return to the form. In the menu, double-click Sort Descending and implement its Click event as follows:
      
    System::Void mnuSortDesc_Click(System::Object *  sender, System::EventArgs *  e)

{

	 this->dvwStudents->Sort = String::Concat(colSelected->ColumnName, S" DESC");

}
  16. Return to the form. In the menu, double-click Remove Filter/Sort and implement its Click event as follows:
      
    System::Void mnuRemFiltSort_Click(System::Object *  sender, System::EventArgs *  e)

{

	 this->dvwStudents->RowFilter = 0;

	 this->dvwStudents->Sort = String::Concat(S"StudentID", S" ASC");

}
  17. Return to the form and double-click the Close button
  18. Implement its Click event as follows:
      
    System::Void btnClose_Click(System::Object *  sender, System::EventArgs *  e)

{

	 Close();

}
  19. Execute the application
  20. Test the top combo boxes and test the context menu 
 
Previous Copyright © 2005-2016, FunctionX Next