Home

ADO.NET Programming: Sorting Records

   

Fundamentals of Sorting Records

 

Introduction

The lists of records we get with a SELECT statement are presented in the order they have in the table. SQL allows you to arrange records in alphabetical order, in chronological order or in numeric incremental order. After selecting a series of columns, you may want to list the records following an alphabetical order from one specific field. To get an alphabetical or an incremental order of records, you must let the database know what field would be used as reference. This is referred to as sorting the records.

   

Visually Sorting Records

To specify the order, if you are using a table window in Microsoft SQL Server Management Studio or in Microsoft Visual Studio:

  • In the Diagram section, you can right-click a field and select either Sort Ascending or Sort Descending
  • In the Criteria section of the window, under the Sort Type column, click the corresponding box of the desired column. This would reveal that it is a combo box. Then click the arrow of that combo box and make your selection between Ascending and Descending:

Using the Table Window

If you select Ascending or Sort Ascending, the list of records would be re-arranged based on the type of the selected column:

  • If the column is text-based (char, varchar, and their variants), the records would be arranged in alphabetical order
  • If the column is date or time-based (datetime or smalldatetime), the records would be arranged in chronological order
  • If the column is number-based, the records would be arranged in incremental order
  • If the column is Boolean-based (bit), the FALSE records would appear first

If you select Descending or Sort Descending, the list of records would be re-arranged based on the type of the selected column:

  • If the column is text-based (char, varchar, and their variants), the records would be arranged in reverse alphabetical order
  • If the column is date or time-based (datetime or smalldatetime), the records would be arranged in reverse chronological order
  • If the column is number-based, the records would be arranged in decremental order
  • If the column is Boolean-based (bit), the TRUE records would appear first

After selecting the desired Sort Type, you can execute the SQL statement.

Sorting the Records in SQL

In SQL, to specify the sorting order, use the ORDER BY expression. The formula to follow is:

SELECT What FROM WhatObject ORDER BY WhatField;

The column used as the basis must be recognized as part of the selected columns. For example, to get a list of students in alphabetical order based on the LastName column, you can use the following statement:

SELECT FirstName, 
       LastName, 
       DateOfBirth, 
       Gender
FROM   Students
ORDER BY LastName;

This would produce:

Using the SQL

In the same way, you can get the list of girls followed by the list of boys by ordering the list in alphabetical order based on the Sex column. The statement to get this result can be written as follows:

SELECT FirstName, LastName, Gender, EmailAddress
FROM   Students
ORDER BY Gender

As another example, to list all students arranged in alphabetical order by their last name, you can change the statement as follows:

SELECT * FROM Students
ORDER BY LastName

By default, records are ordered in Ascending order. Nevertheless, the Ascending order is controlled using the ASC keyword specified after the based field. For example, to sort the last names in Ascending order including the first and last names, you would use a statement as follows:

SELECT * FROM Students
ORDER BY LastName ASC

On the other hand, if you want to sort records in reverse order, you can use the DESC keyword instead. It produces the opposite result to the ASC effect. Here is an example:

SELECT FirstName,
       LastName,
       Gender,
       ParentsNames,
       SPHome
FROM   Students
ORDER BY LastName DESC;

This would produce:

Sorting Records

Sorting the Records in the Data Grid View

If you use a data grid view in your application, you can sort records without writing a single line of code. To sort the records based on a particular column, click the column header. After clicking for the first time, the column is sorted alphabetically, incrementally, or chronologically and an up-pointing arrow button would appear on the column header. Here is an example on the City column:

Sorting Records Using the Data Grid View

To sort records in reverse order based on a particular column, you can click the column again. Or, you must first click the column header to sort in order, then click the same column header again to reverse. When the records are sorted in reverse, a down-pointing arrow button would appear on the column header. Here is an example on the ZIPCode column:

Sorting Records Using the Data Grid View

Sorting Records Based on Type

 

Null Fields

We already know that some fields can hold a value or be null, which would indicate that the field has no value. As mentioned already, to sort records, you must specify the column by which you are sorting. If some records of that field are null, those records would be selected first. Here is an example:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnShow;
    DataGridView dgvStudents;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnShow = new Button();
        btnShow.Text = "Database";
        btnShow.Location = new Point(12, 12);
        btnShow.Click += new EventHandler(btnShowClick);

        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 40);
        dgvStudents.Size = new System.Drawing.Size(270, 220);
        dgvStudents.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;

        Text = "Sorting Records";
        Controls.Add(btnShow);
        Controls.Add(dgvStudents);
    }

    void btnShowClick(object sender, EventArgs e)
    {
        using (SqlConnection cntStudents =
                new SqlConnection("Data Source=(local);" +
                                  "Database='rosh';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdStudents =
                        new SqlCommand("SELECT * FROM Registration.Students;",
                                       cntStudents);
            SqlDataAdapter sdaStudents = new SqlDataAdapter();
            DataSet dsStudents = new DataSet("StudentsSet");

            cntStudents.Open();

            sdaStudents.SelectCommand = cmdStudents;
            sdaStudents.Fill(dsStudents);

            dgvStudents.DataSource = dsStudents.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

Sorting Records

On the other hand, if you sort the records in descending order, the non-null records would come first.

Sorting String-Based Fields

If you sort the records based on a column that uses plain text (char, varchar, text and their variants nchar, nvarchar, and ntext), the database engine would refer to the language used by the database. If the language is latin-based, which is the default in US English, the records would be arranged in alphabetical order based on the indicated column.

Here is an example that gives a list of students based on the students numbers:

void btnShowClick(object sender, EventArgs e)
{
    using (SqlConnection cntStudents =
                new SqlConnection("Data Source=(local);" +
                                  "Database='rosh';" +
                                  "Integrated Security=yes;"))
    {
        SqlCommand cmdStudents =
            new SqlCommand("SELECT StudentNumber, FirstName, LastName, " +
                           "Gender, ParentsNames, SingleParentHome " +
                           "FROM Registration.Students " +
                           "ORDER BY StudentNumber;",
                            cntStudents);
        SqlDataAdapter sdaStudents = new SqlDataAdapter();
        DataSet dsStudents = new DataSet("StudentsSet");

        cntStudents.Open();

        sdaStudents.SelectCommand = cmdStudents;
        sdaStudents.Fill(dsStudents);

        dgvStudents.DataSource = dsStudents.Tables[0];
    }
}

This would produce:

Sorting Records

As mentioned already, if the column has null values, their records would come first. Also, you can add the ASC keyword to re-enforce the idea that you want to sort the records in ascending order.

To reverse an ascending arrangement, add the DESC keyword after the name of the column. Here is an example:

void btnShowClick(object sender, EventArgs e)
{
    using (SqlConnection cntStudents =
                new SqlConnection("Data Source=(local);" +
                                  "Database='rosh';" +
                                  "Integrated Security=yes;"))
    {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT FirstName," +
                               "       LastName," +
                               "       Gender," +
                               "       ParentsNames," +
                               "       SingleParentHome " +
                               "FROM   Registration.Students " +
                               "ORDER BY LastName DESC;",
                                cntStudents);
            SqlDataAdapter sdaStudents = new SqlDataAdapter();
            DataSet dsStudents = new DataSet("StudentsSet");

            cntStudents.Open();

            sdaStudents.SelectCommand = cmdStudents;
            sdaStudents.Fill(dsStudents);

            dgvStudents.DataSource = dsStudents.Tables[0];
    }
}

This would produce:

Sorting Records

Sorting Boolean Fields

Boolean fields are those that use 0 (false) and 1 (true) values. In a data grid view, they appear with check boxes. If you arrange a list based on such a field, the NULL records would come first, followed by records with a false (unchecked) value, and followed by records with a true (checked) value. Here is an example:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnCreateTable;
    Button btnSort;
    DataGridView dgvStudents;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreateTable = new Button();
        btnCreateTable.Width = 120;
        btnCreateTable.Text = "Create Table";
        btnCreateTable.Location = new Point(12, 12);
        btnCreateTable.Click += new EventHandler(btnCreateClick);

        btnSort = new Button();
        btnSort.Text = "Sort";
        btnSort.Location = new Point(140, 12);
        btnSort.Click += new EventHandler(btnSortClick);

        dgvStudents = new DataGridView();
        dgvStudents.Location = new Point(12, 44);
        dgvStudents.Size = new System.Drawing.Size(270, 220);
        dgvStudents.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;

        Text = "Sorting Records";
        Controls.Add(btnCreateTable);
        Controls.Add(btnSort);
        Controls.Add(dgvStudents);
    }

    void btnCreateClick(object sender, EventArgs e)
    {
        using (SqlConnection cntStudents =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdStudents =
                new SqlCommand("CREATE TABLE Videos(Title nvarchar(50), [Length] int," +
                               "Rating nchar(10), [Year] int, WideScreen bit);" +
                               "INSERT INTO Videos(Title, [Length], Rating, [Year], WideScreen) " +
                               "VALUES('Last Castle (The)', 133, 'R', 2001, 1);" +
                               "INSERT INTO Videos(Title, [Length], [Year])" +
                               "VALUES('Sex, Lies, and Videotape', 99, 1989);" +
                               "INSERT INTO Videos(Title, [Length], [Year], WideScreen)" +
                               "VALUES('American President (The)', 115, 1995, 0);" +
                               "INSERT INTO Videos(Title, WideScreen, Rating)" +
                               "VALUES('Day After Tomorrow (The)', 1, 'PG-13');" +
                               "INSERT INTO Videos(Title, [Length], Rating, WideScreen)" +
                               "VALUES('Sneakers', 126, 'PG-13', 1);",
                                cntStudents);

            cntStudents.Open();
            cmdStudents.ExecuteNonQuery();

            MessageBox.Show("The Videos table has been created",
                            "Video Collection",
                             MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntStudents =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT * FROM Videos " +
                               "ORDER BY WideScreen;",
                                cntStudents);
            SqlDataAdapter sdaStudents = new SqlDataAdapter();
            DataSet dsStudents = new DataSet("StudentsSet");

            cntStudents.Open();

            sdaStudents.SelectCommand = cmdStudents;
            sdaStudents.Fill(dsStudents);

            dgvStudents.DataSource = dsStudents.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

Sorting Records

If you sort the records in descending order, the records with 1 (true or unchecked) value would come up first, followed by those with 0 (false unchecked), and then the NULL values.

Sorting Number-Based Fields

As you may know already, the SQL supports various types of numeric values. The fields that use those values can be sorted in incremental order. The SQL interpreter uses the rules specified in the Control Panel. For example, in US English, the referenced number is 0. Then there are negative and positive values. Of course, negative values come before 0 and positive values come after.

As seen with other types, if you sort the records based on a number-based column, if that column has null records, those records would come first. The other records would be sorted in increment order. Here is an example:

void btnSortClick(object sender, EventArgs e)
{
    using (SqlConnection cntStudents =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
    {
            SqlCommand cmdStudents =
                new SqlCommand("SELECT * FROM Videos " +
                               "ORDER BY [Year];",
                                cntStudents);
            SqlDataAdapter sdaStudents = new SqlDataAdapter();
            DataSet dsStudents = new DataSet("StudentsSet");

            cntStudents.Open();

            sdaStudents.SelectCommand = cmdStudents;
            sdaStudents.Fill(dsStudents);

            dgvStudents.DataSource = dsStudents.Tables[0];
    }
}

This would produce:

Sorting Records

Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.

Date and Time-Based Fields

The SQL supports date, time, and combinations of date and time values. When you sort records based on a column that uses one of Transact-SQL date-based data types (date, time, datetime, smalldate, or datetime2), the SQL interpreter must be able to identify each date/time value. Fortunately, the database engine can validate each date/time value and reject those that are not valid.

As seen for the other data types, if you sort records based on a column that uses a date/time type and if the column has null values, the records with null values would show first. The values of a date/time field are sorted in chronological orders. As a result:

  • If the values are used date values only, the records that occur first would also be the first to come up, incrementally. Here is an example:
    using System;
    using System.Drawing;
    using System.Windows.Forms;
    using System.Data;
    using System.Data.SqlClient;
    
    public class Exercise : System.Windows.Forms.Form
    {
        Button btnCreate;
        Button btnSort;
        DataGridView dgvIceCream;
    
        public Exercise()
        {
            InitializeComponent();
        }
    
        void InitializeComponent()
        {
            btnCreate = new Button();
            btnCreate.Width = 120;
            btnCreate.Text = "Create Table";
            btnCreate.Location = new Point(12, 12);
            btnCreate.Click += new EventHandler(btnCreateClick);
    
            btnSort = new Button();
            btnSort.Text = "Sort";
            btnSort.Location = new Point(140, 12);
            btnSort.Click += new EventHandler(btnSortClick);
    
            dgvIceCream = new DataGridView();
            dgvIceCream.Location = new Point(12, 44);
            dgvIceCream.Size = new System.Drawing.Size(270, 220);
            dgvIceCream.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                                 AnchorStyles.Right | AnchorStyles.Bottom;
    
            Text = "Sorting Records";
            Controls.Add(btnCreate);
            Controls.Add(btnSort);
            Controls.Add(dgvIceCream);
        }
    
        void btnCreateClick(object sender, EventArgs e)
        {
            using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Integrated Security='SSPI';"))
           {    
                SqlCommand cmdIceCream =
                        new SqlCommand("CREATE DATABASE IceCreamFactory;",
                            cntIceCream);
    
                cntIceCream.Open();
                cmdIceCream.ExecuteNonQuery();
    
                MessageBox.Show("A database named IceCreamFactory has been created.",
                                "Solas Property Rental",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
    
            using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='IceCreamFactory';" +
                                      "Integrated Security=yes;"))
            {
                SqlCommand cmdIceCream =
                    new SqlCommand("CREATE SCHEMA IceCream;",
                                    cntIceCream);
    
                cntIceCream.Open();
                cmdIceCream.ExecuteNonQuery();
    
                MessageBox.Show("A schema named IceCream table has been created",
                                "Ice Cream Factory",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
    
            using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='IceCreamFactory';" +
                                      "Integrated Security=yes;"))
            {
                SqlCommand cmdIceCream =
                    new SqlCommand("CREATE TABLE IceCream.Orders1" +
                                   "(" +
                                   "    OrderID int identity(1, 1) not null, " +
                                   "    OrderDate date not null, OrderTime time null " +
                                   "); " +
                                   "INSERT IceCream.Orders1(OrderDate, OrderTime) " +
                                   "VALUES(N'2011-02-14', N'10:12'), (N'2011-02-15', N'09:08'), " +
                                   "      (N'2011-05-10', N'15:24'), (N'2011-07-04', N'14:01'), " +
                                   "      (N'2011-04-18', N'19:16'), (N'2011-04-18', N'09:15'), " +
                                   "      (N'2011-04-18', N'12:48'), (N'2011-07-04', N'11:26');",
                                    cntIceCream);
    
                cntIceCream.Open();
                cmdIceCream.ExecuteNonQuery();
    
                MessageBox.Show("A table named Orders has been created in the IceCream schema",
                                "Ice Cream Factory",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    
        void btnSortClick(object sender, EventArgs e)
        {
            using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='IceCreamFactory';" +
                                      "Integrated Security=yes;"))
            {
                SqlCommand cmdIceCream =
                    new SqlCommand("SELECT OrderID, OrderDate FROM IceCream.Orders1 " +
                                   "ORDER BY OrderDate;",
                                    cntIceCream);
                SqlDataAdapter sdaIceCream = new SqlDataAdapter();
                DataSet dsIceCream = new DataSet("IceCreamSet");
    
                cntIceCream.Open();
    
                sdaIceCream.SelectCommand = cmdIceCream;
                sdaIceCream.Fill(dsIceCream);
    
                dgvIceCream.DataSource = dsIceCream.Tables[0];
            }
        }
    
        public static int Main()
        {
            System.Windows.Forms.Application.Run(new Exercise());
            return 0;
        }
    }

    This would produce

    Sorting Records

  • If the values are time-based only, the first thing to check is whether the table also includes another column that uses date values. If the table has only time values, the database engine would (or may) consider that all time values occur on the same day. In that case (or if that's the case), if you sort the records on the column that has the time values, the values closer to 0:01 AM would first appear, then the values that occur closer to 23:59 (midnight) on the same day would appear last. Here is an example:
    void btnCreateClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='IceCreamFactory';" +
                                      "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                    new SqlCommand("CREATE TABLE IceCream.Orders2" +
                                   "(" +
                                   "    OrderID int identity(1, 1) not null," +
                                   "    OrderTime time not null" +
                                   ");" +
                                   "INSERT IceCream.Orders2(OrderTime)" +
                                   "VALUES(N'10:12'), (N'09:08'), (N'15:24'), (N'14:01')," +
                                   "      (N'19:16'), (N'10:12'), (N'12:48'), (N'11:26');",
                                    cntIceCream);
    
            cntIceCream.Open();
            cmdIceCream.ExecuteNonQuery();
    
            MessageBox.Show("A table named Orders has been created in the IceCream schema",
                            "Ice Cream Factory",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
    
    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='IceCreamFactory';" +
                                      "Integrated Security=yes;"))
        {
                SqlCommand cmdIceCream =
                    new SqlCommand("SELECT OrderID, OrderTime FROM IceCream.Orders2 " +
                                   "ORDER BY OrderTime;",
                                    cntIceCream);
                SqlDataAdapter sdaIceCream = new SqlDataAdapter();
                DataSet dsIceCream = new DataSet("IceCreamSet");
    
                cntIceCream.Open();
    
                sdaIceCream.SelectCommand = cmdIceCream;
                sdaIceCream.Fill(dsIceCream);
    
                dgvIceCream.DataSource = dsIceCream.Tables[0];
        }
    }

    This would produce:

    Sorting Records

  • If the values are combinations of date and time values, if some records occur on the same day but at different times, the records would be sorted based on time. That is, the records with similar dates would be grouped; then inside a group, the records are sorted by time. Consider the following example:
    void btnCreateClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='IceCreamFactory';" +
                                      "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                    new SqlCommand("CREATE TABLE IceCream.Orders3" +
                                   "(" + 
                                   "    OrderID int identity(1, 1) not null," +
                                   "    OrderDate datetime not null" +
                                   ");" +
                                   "INSERT IceCream.Orders3(OrderDate)" +
                                   "VALUES(N'2011-02-14 10:12'), (N'2011-02-15 09:08')," +
                                   "      (N'2011-05-10 15:24'), (N'2011-07-04 14:01')," +
                                   "      (N'2011-04-18 19:16'), (N'2011-04-18 10:12')," +
                                   "      (N'2011-04-18 12:48'), (N'2011-07-04 11:26');",
                                   cntIceCream);
    
            cntIceCream.Open();
            cmdIceCream.ExecuteNonQuery();
    
            MessageBox.Show("A table named Orders has been created in the IceCream schema",
                            "Ice Cream Factory",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
    
    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                new SqlConnection("Data Source=(local);" +
                                  "Database='IceCreamFactory';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                    new SqlCommand("SELECT OrderID, OrderDate FROM IceCream.Orders3 " +
                                   "ORDER BY OrderDate;",
                                    cntIceCream);
            SqlDataAdapter sdaIceCream = new SqlDataAdapter();
            DataSet dsIceCream = new DataSet("IceCreamSet");
    
            cntIceCream.Open();
    
            sdaIceCream.SelectCommand = cmdIceCream;
            sdaIceCream.Fill(dsIceCream);
    
            dgvIceCream.DataSource = dsIceCream.Tables[0];
        }
    }

    This would produce:

    Sorting Records

Sorting More Than One Column

 

Introduction

Consider the following table:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnCreate;
    Button btnSort;
    DataGridView dgvExercise;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreate = new Button();
        btnCreate.Width = 120;
        btnCreate.Text = "Create Table";
        btnCreate.Location = new Point(12, 12);
        btnCreate.Click += new EventHandler(btnCreateClick);

        btnSort = new Button();
        btnSort.Text = "Sort";
        btnSort.Location = new Point(140, 12);
        btnSort.Click += new EventHandler(btnSortClick);

        dgvExercise = new DataGridView();
        dgvExercise.Location = new Point(12, 44);
        dgvExercise.Size = new System.Drawing.Size(270, 220);
        dgvExercise.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;

        Text = "Sorting Records";
        Controls.Add(btnCreate);
        Controls.Add(btnSort);
        Controls.Add(dgvExercise);
    }

    void btnCreateClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE TABLE Employees([Empl #] nchar(5), [First Name] nvarchar(20), " +
                               "[Last Name] nvarchar(20), Salary money, [Full Time?] bit);" +
                               "INSERT INTO Employees " +
                               "VALUES('29730', 'Philippe', 'Addy', 20.05, 1);" +
                               "INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)" +
                               "VALUES('28084', 'Joan', 'Shepherd', 12.72), " +
                               "      ('79272', 'Joshua', 'Anderson', 18.26);" +
                               "INSERT INTO Employees " +
                               "VALUES('22803', 'Gregory', 'Swanson', 15.95, 0);" +
                               "INSERT INTO Employees([Empl #], [Last Name], Salary, [Full Time?])" +
                               "VALUES('28084', 'Shepherd', 12.72, 1)," +
                               "      ('39742', 'Anders', 8.88, 0);" +
                               "INSERT INTO Employees " +
                               "VALUES('83084', 'Josephine', N'Anderson', 20.02, 1);" +
                               "INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)" +
                               "VALUES('79272', 'James', 'Anders', 18.26)," +
                               "      ('27924', 'Gregory', 'Hope', 12.85)," +
                               "      ('39742', 'John', 'Anderson', 8.88);",
                                cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();

            MessageBox.Show("A table named Employees has been created.",
                            "Exercise",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("SELECT * FROM Employees;",
                                cntExercise);
            SqlDataAdapter sdaExercise = new SqlDataAdapter();
            DataSet dsExercise = new DataSet("IceCreamSet");

            cntExercise.Open();

            sdaExercise.SelectCommand = cmdExercise;
            sdaExercise.Fill(dsExercise);

            dgvExercise.DataSource = dsExercise.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

Sorting Records

Imagine you want to arrange the list based on salaries, you would execute a statement as:

void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("SELECT [Empl #], [First Name], [Last Name], [Salary], " +
                               "CASE [Full Time?] " +
                               "    WHEN 0 THEN N'Contractor'  " +
                               "    WHEN 1 THEN N'Full Time'  " +
                               "    ELSE N'Unspecified'  " +
                               "END AS [Employment Status]  " +
                               "FROM Employees  " +
                               "ORDER BY [Salary];",
                                cntExercise);
            SqlDataAdapter sdaExercise = new SqlDataAdapter();
            DataSet dsExercise = new DataSet("IceCreamSet");

            cntExercise.Open();

            sdaExercise.SelectCommand = cmdExercise;
            sdaExercise.Fill(dsExercise);

            dgvExercise.DataSource = dsExercise.Tables[0];
        }
    }

This would produce:

Sorting Records

Notice that some records have the same salaries. If you get a situation where many records on a column have the same value, you can specify an additional column by which to sort the records.

To visually sort by more than one column, in the Criteria pane, click the Sort Type corresponding to the first column and select the desired option. To specify the subsequent column, click the box corresponding to its Sort Type and select the desired option. To keep track of the columns you are using, in the Sort Order column, the database engine would create an incrementing number for each. The first column receives the number 1, the second receives the number 2, and so on. Here is an example:

Sorting Order

If you don't like the order suggested, click the Sort Order box corresponding to the column whose position you want to change, then click the arrow of its combo box and select the desired number:

Sorting Order

After making your selection, the studio would update the order of sorting columns.

To arrange the list using more than one column using the SQL, after ORDER BY, type the columns separated by commas.

Sorting With Non-NULL and NULL Fields

If you specify more than one record to sort by, the database engine sorts the primary column first. Then, on the second field, when two records have the same value, the NULL values would come first. Here is an example:

void btnSortClick(object sender, EventArgs e)
{
    using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
    {
        SqlCommand cmdExercise =
                new SqlCommand("SELECT [Empl #], [First Name], [Last Name], [Salary], [Full Time?] " +
                               "FROM Employees " +
                               "ORDER BY [Salary], [Full Time?];",
                                cntExercise);
        SqlDataAdapter sdaExercise = new SqlDataAdapter();
        DataSet dsExercise = new DataSet("IceCreamSet");

        cntExercise.Open();

        sdaExercise.SelectCommand = cmdExercise;
        sdaExercise.Fill(dsExercise);

        dgvExercise.DataSource = dsExercise.Tables[0];
    }
}

This would produce:

Sorting Records

Notice that when two records have the same values and if one of the records has a NULL value, that one comes first.

Sorting Two String-Based Columns

Imagine you have two string-based records that have the same value. If you sort them, you would wonder which one would come up first. An additional field would solve this problem. That is, you can combine fields to sort the records. Here is an example:

void btnSortClick(object sender, EventArgs e)
{
    using (SqlConnection cntExercise =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
    {
        SqlCommand cmdExercise =
            new SqlCommand("SELECT [Empl #], [First Name], [Last Name], [Salary], " +
                           "CASE [Full Time?] " +
                           "    WHEN 0 THEN N'No' " +
                           "    WHEN 1 THEN N'Yes' " +
                           "    ELSE N'Don''t Know' " +
                           "END AS [Employment Status] " +
                           "FROM Employees " +
                           "ORDER BY [Last Name], [First Name]; ",
                                cntExercise);
            SqlDataAdapter sdaExercise = new SqlDataAdapter();
            DataSet dsExercise = new DataSet("IceCreamSet");

            cntExercise.Open();

            sdaExercise.SelectCommand = cmdExercise;
            sdaExercise.Fill(dsExercise);

            dgvExercise.DataSource = dsExercise.Tables[0];
    }
}

If you do this, the SQL interpreter would first sort the records based on the first field, in which case the records would be grouped. It is then the second field that would be used to handle the assignment. In other words, using the alphabetical order, the value that comes first would be based on the alphabet, such as US English. The above statement would produce:

Using the SQL to Sort Records

Notice that, when you sort more than one string-based fields, the records with NULL values come first, such is the case for the above Anders and the Shepherd records. For the fields that are not null, the records are sorted based on the second records; that's the case for the Anderson records.

Sorting Two Date/Time-Based Columns

As you may know already, Transact-SQL supports records that use date only, time only, or a combination of date and time values. When sorting the records, you can combine the fields to get a certain appreciation of the distribution of records. If you have records that occur at different dates, the sorting is easy.

Imagine you have records that occur on the same day but at different times, if you have all dates and times on the same column, you can sort by only that column and the database engine would take care of the rest. We saw an example already.

If you have one column that holds date values but another field that records the times, you can first sort by the date column, followed by the time field. Here is an example:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnCreate;
    Button btnSort;
    DataGridView dgvIceCream;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreate = new Button();
        btnCreate.Width = 120;
        btnCreate.Text = "Create Table";
        btnCreate.Location = new Point(12, 12);
        btnCreate.Click += new EventHandler(btnCreateClick);

        btnSort = new Button();
        btnSort.Text = "Sort";
        btnSort.Location = new Point(140, 12);
        btnSort.Click += new EventHandler(btnSortClick);

        dgvIceCream = new DataGridView();
        dgvIceCream.Location = new Point(12, 44);
        dgvIceCream.Size = new System.Drawing.Size(270, 220);
        dgvIceCream.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;

        Text = "Sorting Records";
        Controls.Add(btnCreate);
        Controls.Add(btnSort);
        Controls.Add(dgvIceCream);
    }

    void btnCreateClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                new SqlConnection("Data Source=(local);" +
                                  "Database='IceCreamFactory';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                new SqlCommand("CREATE TABLE IceCream.Orders4 " +
                               "(" +
                               "    OrderID int identity(1, 1) not null, " +
                               "    OrderDate date not null, " +
                               "    OrderTime time not null " +
                               "); " +
                               "INSERT IceCream.Orders4(OrderDate, OrderTime) " +
                               "VALUES(N'2011-02-14', N'10:12'), (N'2011-02-15', N'09:08'), " +
                               "      (N'2011-05-10', N'15:24'), (N'2011-07-04', N'14:01'), " +
                               "      (N'2011-04-18', N'19:16'), (N'2011-04-18', N'10:12'), " +
                               "      (N'2011-04-18', N'12:48'), (N'2011-07-04', N'11:26');",
                                cntIceCream);

            cntIceCream.Open();
            cmdIceCream.ExecuteNonQuery();

            MessageBox.Show("A table named Orders has been created.",
                            "Ice Cream Factory",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntIceCream =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='IceCreamFactory';" +
                                      "Integrated Security=yes;"))
        {
            SqlCommand cmdIceCream =
                new SqlCommand("SELECT OrderID, OrderDate, OrderTime FROM IceCream.Orders4 " +
                               "ORDER BY OrderDate, OrderTime;",
                               cntIceCream);
            SqlDataAdapter sdaIceCream = new SqlDataAdapter();
            DataSet dsIceCream = new DataSet("IceCreamSet");

            cntIceCream.Open();

            sdaIceCream.SelectCommand = cmdIceCream;
            sdaIceCream.Fill(dsIceCream);

            dgvIceCream.DataSource = dsIceCream.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

In this case, the records of the date column would be sorted first, which means the records would be grouped by day. In other words, records that occur on the same day would be put in the same range. Then, when some records occur on the same day, the records of the time field would be sorted in chronological order. The above code would produce:

Using the SQL to Sort Records

Notice the records registered on 2011-04-18 and 2011-07-04. It's the values of OrderTime field that determine the sorting.

Sorting the Records With Ties

Consider the following table and its records:

CREATE', 133, N'PG', 2004);
GO

As we have seen so far, to get the list of all records, you would execute:

SELECT * FROM Videos;

Videos

Notice that the statement produces 12 records. To get the first 40% records, you would execute:

using System;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    Button btnCreate;
    Button btnSort;
    DataGridView dgvVideos;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        btnCreate = new Button();
        btnCreate.Width = 120;
        btnCreate.Text = "Create Table";
        btnCreate.Location = new Point(12, 12);
        btnCreate.Click += new EventHandler(btnCreateClick);

        btnSort = new Button();
        btnSort.Text = "Sort";
        btnSort.Location = new Point(140, 12);
        btnSort.Click += new EventHandler(btnSortClick);

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 44);
        dgvVideos.Size = new System.Drawing.Size(270, 220);
        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;

        Text = "Sorting Records";
        Controls.Add(btnCreate);
        Controls.Add(btnSort);
        Controls.Add(dgvVideos);
    }

    void btnCreateClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos =
                new SqlConnection("Data Source=(local);" +
                                  "Database='Exercise';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                new SqlCommand("CREATE TABLE Videos " +
                               "( " +
                               "    [Shelf #] nchar(7) null, " +
                               "    Title nvarchar(50) not null, " +
                               "    Director nvarchar(50), " +
                               "    [Length] int, " +
                               "    Rating nchar(10), " +
                               "    [Year] int " +
                               "); " +
                               "INSERT INTO Videos " +
                               "VALUES(N'DHE-927', N'Two for the Money', N'D.J. Caruso', 123, N'R', 2008), " +
                               "      (N'CGM-683', N'Her Alibi', N'Bruce Beresford', 94, N'PG-13', 1998), " +
                               "      (N'FQT-973', N'Memoirs of a Geisha', N'Rob Marshall', 145, N'PG-13', 2006), " +
                               "      (N'DBT-395', N'Wall Street', N'Oliver Stone', 126, N'R', 2000); " +
                               "INSERT INTO Videos(Title, Director, [Length], Rating) " +
                               "VALUES(N'Stealing Harvard', N'Bruce McCulloch', 85, N'PG-13'); " +
                               "INSERT INTO Videos([Shelf #], Title, Director, [Length], [Year]) " +
                               "VALUES(N'TPH-973', N'A Few Good Men', N'Rob Reiner', 138, 1992); " +
                               "INSERT INTO Videos(Title, Director, [Year], [Length]) " +
                               "VALUES(N'The Silence of the Lambs', N'Jonathan Demme', 1991, 118); " +
                               "INSERT INTO Videos([Shelf #], Title, Director, Rating, [Length]) " +
                               "VALUES(N'DZV-737', N'The Lady Killers', N'Joel Coen & Ethan Coen', N'R', 104); " +
                               "INSERT INTO Videos(Title, Director, [Length],  Rating, [Year]) " +
                               "VALUES(N'Sneakers', N'Phil Alden Robinson', 126, N'PG-13', 1992), " +
                               "      (N'Annie', N'John Huston', 126, N'G', 1982), " +
                               "      (N'Dave', N'Ivan Reitman', 110, N'PG-13', 1993); " +
                               "INSERT INTO Videos " +
                               "VALUES(N'ADR-737', N'Incredibles (The)', N'Brad Bird', 133, N'PG', 2004);",
                                cntVideos);

            cntVideos.Open();
            cmdVideos.ExecuteNonQuery();

            MessageBox.Show("A table named Videos has been created.",
                            "Video Collection",
                            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }

    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='Exercise';" +
                                      "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                new SqlCommand("SELECT * FROM Videos;", cntVideos);
            SqlDataAdapter sdaVideos = new SqlDataAdapter();
            DataSet dsVideos = new DataSet("IceCreamSet");

            cntVideos.Open();

            sdaVideos.SelectCommand = cmdVideos;
            sdaVideos.Fill(dsVideos);

            dgvVideos.DataSource = dsVideos.Tables[0];
        }
    }

    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

Videos

Notice that you get 5 records that include 3 with a PG-13 rating. If you want to arrange the list based on the Rating column, you can add the ORDER BY clause as follows:

void btnSortClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos =
        new SqlConnection("Data Source=(local);" +
                          "Database='Exercise';" +
                          "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
                new SqlCommand("SELECT TOP 40 PERCENT * " +
                               "FROM Videos " +
                               "ORDER BY Rating;", cntVideos);
        SqlDataAdapter sdaVideos = new SqlDataAdapter();
        DataSet dsVideos = new DataSet("IceCreamSet");
        
        cntVideos.Open();
        
        sdaVideos.SelectCommand = cmdVideos;
        sdaVideos.Fill(dsVideos);
        
        dgvVideos.DataSource = dsVideos.Tables[0];
    }
}

Videos

Notice that you still get 5 records but this time, only one is with PG-13 and the PG-13 record is the last. Transact-SQL provides an operation that associates with the ORDER BY statement and the TOP PERCENT value. The operation works as follows:

  1. First select the top expression percent records
  2. Second, based on the column used by the ORDER BY clause, show all records that use the value of that column, even if the result will produce more records than the specified percent value

To perform this operation, the formula to follow is:

SELECT TOP ( expression ) [ PERCENT ] [ WITH TIES ]
What Columns
FROM Object
ORDER BY Column

The WITH TIES clause asks the SELECT statement to perform the two operations we saw aboce. The WITH TIES expression is entered after the PERCENT keyword, which is before the list of columns. Here is an example:

void btnSortClick(object sender, EventArgs e)
{
    using (SqlConnection cntVideos =
        new SqlConnection("Data Source=(local);" +
                          "Database='Exercise';" +
                          "Integrated Security=yes;"))
    {
        SqlCommand cmdVideos =
                new SqlCommand("SELECT TOP 40 PERCENT WITH TIES * " +
                               "FROM Videos " +
                               "ORDER BY Rating;", cntVideos);
        SqlDataAdapter sdaVideos = new SqlDataAdapter();
        DataSet dsVideos = new DataSet("IceCreamSet");
        
        cntVideos.Open();
        
        sdaVideos.SelectCommand = cmdVideos;
        sdaVideos.Fill(dsVideos);
        
        dgvVideos.DataSource = dsVideos.Tables[0];
    }
}

This would produce:

Videos

Consequently, the WITH TIES condition is used to select the top percent records plus all records that use the value of the last record depending on the column specified by the ORDER BY clause.

 

Home Copyright © 2010-2011 C#Key