To specify the order, if you are using a table window in Microsoft SQL Server Management Studio or in Microsoft Visual Studio:
If you select Ascending or Sort Ascending, the list of records would be re-arranged based on the type of the selected column:
If you select Descending or Sort Descending, 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,
Gender
FROM Students
ORDER BY LastName;
This would produce:
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:
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:
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:
On the other hand, if you sort the records in descending order, the non-null records would come first.
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:
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:
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:
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.
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:
Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.
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:
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:
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:
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:
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:
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. 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:
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=(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:
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. 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:
Notice the records registered on 2011-04-18 and 2011-07-04. It's the values of OrderTime field that determine the sorting.
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 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;
}
}
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];
}
}
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=(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:
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.
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
