Home

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. The 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.

Consider the following list of records

Name Date Hired Salary Gender Is Married
Judie 10/22/2006 18.15 Female Yes
Ernest 05/18/2002 24.04 Male No
Bill 08/06/2012 15.25 Unknown No
David 02/28/2013 36.18 Male  
Hermine 06/15/2010 12.16 Unknown Yes

To sort the records of a list, you first must specify the column used as reference. The second option is to sort in ascending or descending order. If you decide to sort in ascending order:

  • 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 sort in descending order, 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, 
       Sex
FROM Students
ORDER BY LastName;

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;

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

 

Sorting 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 (the SQL code ROSH database is available):

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

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

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        dgvStudents = new DataGridView ();
        dgvStudents.Location = new Point(12, 12);

        Text = "Red Oak High School";
        Load += new EventHandler(FormLoaded);
        Controls.Add(dgvStudents);

        StartPosition = FormStartPosition.CenterScreen;
        dgvStudents.Width = this.Width - 30;
        dgvStudents.Height = this.Height - 50;
        dgvStudents.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void FormLoaded(object sender, EventArgs e)
    {
        using (SqlConnection cntStudents =
            new SqlConnection("Data Source='EXPRESSION'; " +
                              "Database='rosh'; " +
                              "Integrated Security='SSPI';"))
        {
            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 class Program
{
    [STAThread]
    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 FormLoaded(object sender, EventArgs e)
{
    using (SqlConnection cntStudents =
            new SqlConnection("Data Source='EXPRESSION'; " +
                              "Database='rosh'; " +
                              "Integrated Security='SSPI';"))
    {
        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 FormLoaded(object sender, EventArgs e)
{
    using (SqlConnection cntStudents =
            new SqlConnection("Data Source='EXPRESSION'; " +
                              "Database='rosh'; " +
                              "Integrated Security='SSPI';"))
    {
            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.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

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

    public Exercise()
    {
        InitializeComponent();
    }

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

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

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 46);

        Text = "Video Collection";
        Controls.Add(btnCreateTable);
        Controls.Add(btnSort);
        Controls.Add(dgvVideos);

        StartPosition = FormStartPosition.CenterScreen;
        dgvVideos.Width = this.Width - 30;
        dgvVideos.Height = this.Height - 80;
        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
    }

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

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

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

    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                new SqlCommand("SELECT * FROM Videos " +
                               "ORDER BY WideScreen;",
                                cntVideos);
            SqlDataAdapter sdaVideos = new SqlDataAdapter();
            DataSet dsVideos = new DataSet("VideosSet");

            cntVideos.Open();

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

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

public class Program
{
    [STAThread]
    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 cntVideos =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=yes;"))
    {
            SqlCommand cmdVideos =
                new SqlCommand("SELECT * FROM Videos " +
                               "ORDER BY [Year];",
                                cntVideos);
            SqlDataAdapter sdaVideos = new SqlDataAdapter();
            DataSet dsVideos = new DataSet("VideosSet");

            cntVideos.Open();

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

            dgvVideos.DataSource = dsVideos.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.

Sorting More Than One Column

 

Introduction

Consider the following table:

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

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

    public Exercise()
    {
        InitializeComponent();
    }

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

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

        dgvEmployees = new DataGridView();
        dgvEmployees.Location = new Point(12, 46);

        Text = "Ice Cream Factory";
        Controls.Add(btnCreateTable);
        Controls.Add(btnSort);
        Controls.Add(dgvEmployees);

        StartPosition = FormStartPosition.CenterScreen;
        dgvEmployees.Width = this.Width - 30;
        dgvEmployees.Height = this.Height - 80;
        dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void btnCreateTableClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE SCHEMA Management;", cntExercise);

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

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

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

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

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

            cntExercise.Open();

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

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

public class Program
{
    [STAThread]
    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='EXPRESSION';" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=yes;"))
    {
            SqlCommand cmdExercise =
                new SqlCommand("SELECT [Empl #], [First Name], [Last Name], Salary " +
                               "FROM Management.Employees  " +
                               "ORDER BY Salary;",
                                cntExercise);
            SqlDataAdapter sdaExercise = new SqlDataAdapter();
            DataSet dsExercise = new DataSet("EmployeesSet");

            cntExercise.Open();

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

            dgvEmployees.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 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='EXPRESSION';" +
                                   "Database='Exercise1';" +
                                   "Integrated Security=yes;"))
    {
            SqlCommand cmdExercise =
                    new SqlCommand("SELECT [Empl #], [First Name], [Last Name], Salary, [Full Time?] " +
                                   "FROM Management.Employees " +
                                   "ORDER BY Salary, [Full Time?];",
                                    cntExercise);
            SqlDataAdapter sdaExercise = new SqlDataAdapter();
            DataSet dsExercise = new DataSet("IceCreamSet");

            cntExercise.Open();

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

            dgvEmployees.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='EXPRESSION';" +
                                  "Database='Exercise1';" +
                                  "Integrated Security=yes;"))
    {
            SqlCommand cmdExercise =
            new SqlCommand("SELECT [Empl #], [First Name], [Last Name], Salary " +
                           "FROM Management.Employees " +
                           "ORDER BY [Last Name], [First Name]; ",
                                cntExercise);
            SqlDataAdapter sdaExercise = new SqlDataAdapter();
            DataSet dsExercise = new DataSet("EmployeesSet");

            cntExercise.Open();

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

            dgvEmployees.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.

 
 
 

Options on Sorting Records

     

Sorting by an Expression

When sorting the records, the database engine mostly needs to have a value as reference, the value by which to arrange the values. Based on this, besides, or inside of, (a) column(s), you can use an expression to sort the records. Here is an example:

SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
       si.Category, 
       si.SubCategory "Sub-Category", 
       si.ItemName "Item Name", 
       si.UnitPrice "Unit Price",
       FORMAT(si.DiscountRate, N'P') "Discount Rate"
FROM Inventory.StoreItems si
ORDER BY (si.UnitPrice * si.DiscountRate);
GO

Using a Function to Sort Records

Just as you use an expression as a basis for sorting records, you can use the return value of a function to arrange records. Here is an example:

SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
	   si.Category, 
	   si.SubCategory "Sub-Category", 
	   si.ItemName "Item Name", 
	   si.UnitPrice "Unit Price",
	   si.DiscountRate "DiscountRate"
FROM Inventory.StoreItems si
ORDER BY FORMAT(si.DiscountRate, N'P');
GO

Of course, you can use your own function. You must first create them. Here are two examples of functions:

USE DepartmentStore1;
GO

CREATE FUNCTION Inventory.CalculateDiscountAmount(@UnitPrice money, @DiscountRate decimal(6, 2))
RETURNS money
AS
BEGIN
	RETURN @UnitPrice * @DiscountRate;
END
GO

CREATE FUNCTION Inventory.CalculatePriceAfterDiscount(@UnitPrice money, @DiscountRate decimal(6, 2))
RETURNS money
AS
BEGIN
	RETURN @UnitPrice - (@UnitPrice * @DiscountRate);
END
GO

After creating a function, you can use it in the ORDER BY clause to arrange the records. Here is an example that uses one of the above functions:

USE DepartmentStore1;
GO

SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
       si.Category, 
       si.SubCategory "Sub-Category", 
       si.ItemName "Item Name", 
       si.UnitPrice "Unit Price",
       FORMAT(si.DiscountRate, N'P') "Discount Rate",
       FORMAT(Inventory.CalculateDiscountAmount(si.UnitPrice, si.DiscountRate), N'C') "Discount Amount"
FROM Inventory.StoreItems si
ORDER BY Inventory.CalculateDiscountAmount(si.UnitPrice, si.DiscountRate);
GO

Here is an example that uses the other function:

USE DepartmentStore1;
GO

SELECT si.ItemNumber "Item #",
       si.Manufacturer, 
	   si.Category, 
	   si.SubCategory "Sub-Category", 
	   si.ItemName "Item Name", 
	   si.UnitPrice "Unit Price",
	   FORMAT(si.DiscountRate, N'P') "Discount Rate",
	   FORMAT(Inventory.CalculateDiscountAmount(si.UnitPrice, si.DiscountRate), N'C') "Discount Amount",
	   FORMAT(Inventory.CalculatePriceAfterDiscount(si.UnitPrice, si.DiscountRate), N'C') "After Discount"
FROM Inventory.StoreItems si
ORDER BY Inventory.CalculatePriceAfterDiscount(si.UnitPrice, si.DiscountRate);
GO

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 top 40% records, you would execute:

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

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

    public Exercise()
    {
        InitializeComponent();
    }

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

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

        dgvVideos = new DataGridView();
        dgvVideos.Location = new Point(12, 46);

        Text = "Video Collection";
        Controls.Add(btnCreateTable);
        Controls.Add(btnSort);
        Controls.Add(dgvVideos);

        StartPosition = FormStartPosition.CenterScreen;
        dgvVideos.Width = this.Width - 30;
        dgvVideos.Height = this.Height - 80;
        dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
                             AnchorStyles.Right | AnchorStyles.Bottom;
    }

    void btnCreateTableClick(object sender, EventArgs e)
    {
        using (SqlConnection cntExercise =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='VideoCollection1';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdExercise =
                new SqlCommand("CREATE SCHEMA Collection;", cntExercise);

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

        using (SqlConnection cntVideos =
                new SqlConnection("Data Source='EXPRESSION';" +
                                  "Database='VideoCollection1';" +
                                  "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                new SqlCommand("CREATE TABLE Collection.Videos " +
                               "( " +
                               "    [Shelf #] nchar(7) null, " +
                               "    Title nvarchar(50) not null, " +
                               "    Director nvarchar(50), " +
                               "    [Length] int, " +
                               "    Rating nchar(10), " +
                               "    [Year] int " +
                               ");" +
                               "INSERT INTO Collection.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 Collection.Videos(Title, Director, [Length], Rating) " +
                               "VALUES(N'Stealing Harvard', N'Bruce McCulloch', 85, N'PG-13'); " +
                               "INSERT INTO Collection.Videos([Shelf #], Title, Director, [Length], [Year]) " +
                               "VALUES(N'TPH-973', N'A Few Good Men', N'Rob Reiner', 138, 1992); " +
                               "INSERT INTO Collection.Videos(Title, Director, [Year], [Length]) " +
                               "VALUES(N'The Silence of the Lambs', N'Jonathan Demme', 1991, 118); " +
                               "INSERT INTO Collection.Videos([Shelf #], Title, Director, Rating, [Length]) " +
                               "VALUES(N'DZV-737', N'The Lady Killers', N'Joel Coen & Ethan Coen', N'R', 104); " +
                               "INSERT INTO Collection.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 Collection.Videos " +
                               "VALUES(N'ADR-737', N'Incredibles (The)', N'Brad Bird', 133, N'PG', 2004);",
                                cntVideos);

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

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

    void btnSortClick(object sender, EventArgs e)
    {
        using (SqlConnection cntVideos =
                    new SqlConnection("Data Source='EXPRESSION';" +
                                      "Database='VideoCollection1';" +
                                      "Integrated Security=yes;"))
        {
            SqlCommand cmdVideos =
                new SqlCommand("SELECT TOP 40 PERCENT * FROM Collection.Videos;",
                	       cntVideos);
            SqlDataAdapter sdaVideos = new SqlDataAdapter();
            DataSet dsVideos = new DataSet("VideosSet");

            cntVideos.Open();

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

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

public class Program
{
    [STAThread]
    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='EXPRESSION';" +
                                      "Database='VideoCollection1';" +
                                      "Integrated Security=yes;"))
    {
            SqlCommand cmdVideos =
                new SqlCommand("SELECT TOP 40 PERCENT * " +
                               "FROM Collection.Videos " +
                               "ORDER BY Rating;", cntVideos);
            SqlDataAdapter sdaVideos = new SqlDataAdapter();
            DataSet dsVideos = new DataSet("VideosSet");

            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='EXPRESSION';" +
                                      "Database='VideoCollection1';" +
                                      "Integrated Security=yes;"))
    {
            SqlCommand cmdVideos =
                new SqlCommand("SELECT TOP 40 PERCENT WITH TIES * " +
                               "FROM Collection.Videos " +
                               "ORDER BY Rating;", cntVideos);
            SqlDataAdapter sdaVideos = new SqlDataAdapter();
            DataSet dsVideos = new DataSet("VideosSet");

            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.

Skipping a Number of Records

By default, when you decide to sort records, the database engine sorts all records from the first to the last. As an alternative, you can indicate from one record to start sorting, that is, the number of records to skip before starting to sort. To do this, you use the following formula:

SELECT All options and everything we have seen so far
What Columns
FROM Object
ORDER BY Column
OFFSET Number { ROW | ROWS } ONLY

The last line is the new option of our formula. You start with the OFFSET keyword followed by a constant integer greater than 1. You must terminathe statement with either the ROW ONLY or the ROWS ONLY exprssion; both work the same. Here is an example:

Click the SqLQuery1 tab and type the following code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace OffsetAndFetch1
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            using (SqlConnection cntLambdaSquare =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='LambdaSquare1';" +
                                      "Integrated Security=yes;"))
            {
                SqlCommand cmdApartments =
                    new SqlCommand("SELECT aparts.UnitNumber [Unit #], " +
                                   "       aparts.Bedrooms   Beds, " +
                                   "       aparts.Bathrooms  Baths, " +
                                   "       aparts.Price      [Monthly Rent], " +
                                   "       aparts.Deposit    [Primary Deposit], " +
                                   "       (aparts.Price + aparts.Deposit) [Due Before Moving], " +
                                   "       aparts.Available " +
                                   "FROM Presentation.Units aparts;",
                               cntLambdaSquare);
                SqlDataAdapter sdaApartments = new SqlDataAdapter();
                DataSet dsApartments = new DataSet("ApartmentsSet");

                cntLambdaSquare.Open();

                sdaApartments.SelectCommand = cmdApartments;
                sdaApartments.Fill(dsApartments);

                dgvApartments.DataSource = dsApartments.Tables[0];

                nudRecords.Maximum = dsApartments.Tables[0].Rows.Count;
            }
        }

        private void btnOffset_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntLambdaSquare =
                    new SqlConnection("Data Source=(local);" +
                                      "Database='LambdaSquare1';" +
                                      "Integrated Security=yes;"))
            {
                SqlCommand cmdApartments =
                    new SqlCommand("SELECT aparts.UnitNumber [Unit #], " +
                                   "       aparts.Bedrooms   Beds, " +
                                   "       aparts.Bathrooms  Baths, " +
                                   "       aparts.Price      [Monthly Rent], " +
                                   "       aparts.Deposit    [Primary Deposit], " +
                                   "       (aparts.Price + aparts.Deposit) [Due Before Moving], " +
                                   "       aparts.Available " +
                                   "FROM Presentation.Units aparts " +
                                   "ORDER BY [Monthly Rent] " +
                                   "OFFSET " + nudRecords.Value + " ROWS;",
                               cntLambdaSquare);
                SqlDataAdapter sdaApartments = new SqlDataAdapter();
                DataSet dsApartments = new DataSet("ApartmentsSet");

                cntLambdaSquare.Open();

                sdaApartments.SelectCommand = cmdApartments;
                sdaApartments.Fill(dsApartments);

                dgvApartments.DataSource = dsApartments.Tables[0];
            }
        }
    }
}

Skipping a Number of Records

Sorting the First, or a Sub-Set of, Records

After skipping a certain number of records, you can ask the database engine to sort only a certain number of first records or a number of records after the skipped section. To do this, you can use the following formula:

SELECT All options and everything we have seen so far
What Columns
FROM Object
ORDER BY Column
OFFSET Number ROW | ROWS
FETCH FIRST | NEXT Number ROW | ROWS

Following this formula, after the OFFSET section, type the FETCH FIRST or the FETCH NEXT expression; both produce the same result. This is followed by a constant integer followed by either ROW or the ROWS (both produce the same result). Here is an example:

private void btnFetch_Click(object sender, EventArgs e)
{
    using (SqlConnection cntLambdaSquare =
            new SqlConnection("Data Source=(local);" +
                              "Database='LambdaSquare1';" +
                              "Integrated Security=yes;"))
    {
        SqlCommand cmdApartments =
            new SqlCommand("SELECT aparts.UnitNumber [Unit #], " +
                           "       aparts.Bedrooms   Beds, " +
                           "       aparts.Bathrooms  Baths, " +
                           "       aparts.Price      [Monthly Rent], " +
                           "       aparts.Deposit    [Primary Deposit], " +
                    "       (aparts.Price + aparts.Deposit) [Due Before Moving], " +
                           "       aparts.Available " +
                           "FROM Presentation.Units aparts " +
                           "ORDER BY [Monthly Rent] " +
                           "OFFSET " + nudFetch.Value + " ROWS " +
                           "FETCH FIRST " + nudFirst.Value + " ROWS ONLY;",
                           cntLambdaSquare);
        SqlDataAdapter sdaApartments = new SqlDataAdapter();
        DataSet dsApartments = new DataSet("ApartmentsSet");

        cntLambdaSquare.Open();

        sdaApartments.SelectCommand = cmdApartments;
        sdaApartments.Fill(dsApartments);

        dgvApartments.DataSource = dsApartments.Tables[0];
    }
}

Sorting the First Set of Records

 
 
   
 

Previous Copyright © 2007-2014, FunctionX Next