Sorting Records
|
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 you sort in descending order, the list of records would be re-arranged based on the type of the selected column:
After selecting the desired Sort Type, you can execute the SQL statement.
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:
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 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:
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:
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 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:
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:
Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.
|
Sorting More Than One Column |
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:
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:
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:
Notice that when two records have the same values and if one of the records has a NULL value, that one comes first.
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:
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;
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;
}
}
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];
}
}
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:
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:
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];
}
}
}
}
|
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];
}
}
