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. This is referred to as sorting the records.
Practical Learning: Introducing Sorting Records
USE master; GO CREATE DATABASE AltairRealtors1; GO USE AltairRealtors1; GO CREATE SCHEMA RealEstate; GO CREATE TABLE RealEstate.Properties ( PropertyNumber int, [Address] nvarchar(120), City nvarchar(40), Locality nvarchar(40), County nvarchar(50), [State] nchar(2), ZIPCode nvarchar(10), PropertyType nvarchar(32), Condition nvarchar(120), Bedrooms tinyint, Bathrooms decimal, FinishedBasement bit, IndoorGarage bit, Stories tinyint, YearBuilt smallint, MarketValue integer ); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, County, [State], ZIPCode, IndoorGarage) VALUES(927415, N'4140 Holisto Crt', N'Germantown', N'Montgomery', N'MD', N'20904', 0); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, Stories, YearBuilt, MarketValue) VALUES(395860, N'1508 Quaddra Str #808', N'York', N'Valley View', N'PA', N'17403', N'Condominium', N'Good Shape', 2, 2.5, 0, 8, 1996, 415665); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, County, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue) VALUES(803184, N'284 Bolston Ave', N'Baltimore', N'Hamden', N'Baltimore', N'MD', N'21211', N'Single Family', N'Needs Repair', 2, 2, 0, 1, 4, 2010, 165885); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, County, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, Stories, YearBuilt, MarketValue) VALUES(150281, N'9205 Arrow Consfield Str', N'Bowie', N'Bowie State', N'Prince George', N'MD', N'20715', N'Single Family', N'Excellent', 5, 3.5, 0, 2014, 782575); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, County, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue) VALUES(867703, N'10282 Harrison Drv #406', N'Charleston', N'Hillsdale', N'WV', N'25302', N'Condominium', N'Excellent', 1, 1, 0, 1, 6, 2016, 675225); GO INSERT INTO RealEstate.Properties(PropertyNumber, City, Locality, County, PropertyType, Condition, Bedrooms, FinishedBasement, Stories, YearBuilt) VALUES(372804, N'Columbia', N'Town Center', N'Howard', N'Townhouse', N'Good Shape', 4, 1, 3, 2008); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, County, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue) VALUES(684462, N'7715 Sharron Str', N'College Park', N'University Blvd', N'Prince George', N'MD', N'20740', N'Single Family', N'Excellent', 4, 3.5, 1, 0, 3, 1998, 498675); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, County, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue) VALUES(270394, N'South Ventura Ave', N'Baltimore', N'Garwyn Oaks', N'Baltimore', N'MD', N'21216', N'Townhouse', N'Good Shape', 3, 2.5, 1, 0, 3, 1990, 244955); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue) VALUES(486263, N'8226 Athena Drv NW #404', N'Washington', N'16th Str NW', N'DC', N'20009', N'Condominium', N'Needs Repair', 2, 2, 0, 0, 4, 2000, 318950); GO INSERT INTO RealEstate.Properties(PropertyNumber, City, ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, Stories, YearBuilt, MarketValue) VALUES(925703, N'Bethesda', N'20814', N'Single Family', N'Unknown', 5, 4.5, 1, 3, 2010, 1180500); GO INSERT INTO RealEstate.Properties(PropertyNumber, City, Locality, [State], ZIPCode, PropertyType, Condition, Bedrooms, IndoorGarage, MarketValue) VALUES(206417, N'Baltimore', N'Towson', N'MD', N'21204', N'Condominium', N'Unknown', 2, 1, 215495); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, County, [State], ZIPCode, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt) VALUES(507150, N'14250 Parkdoll Rd', N'Rockville', N'Twinbrook', N'Montgomery', N'MD', N'20854', N'Good Shape', 3, 2.5, 1, 1, 2, 1988); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, County, [State], ZIPCode, PropertyType, Bedrooms, Bathrooms, FinishedBasement, MarketValue) VALUES(240875, N'842 Hempton Street', N'Charleston', N'Kanawha', N'WV', N'25414', N'Townhouse', 3, 2.5, 0, 348500); GO INSERT INTO RealEstate.Properties(PropertyNumber, [Address], City, Locality, County, [State], ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue) VALUES(747472, N'418 Woodmont Ave', N'Hanover', N'McSherrystown', N'York', N'PA', N'17331', N'Single Family', N'Good Shape', 4, 2.50, 1, 0, 2, 1980, 4246605), (304050, N'1008 Coppen Street', N'Silver Spring', N'Aspen Hill', N'Montgomery', N'MD', N'20906', N'Single Family', N'Excellent', 3, 3, 1, 1, 3, 1996, 685755); GO INSERT INTO RealEstate.Properties(PropertyNumber, City, Locality, County, [State], ZIPCode, Bedrooms, Bathrooms) VALUES(269384, N'Gettysburg', N'Round Top', N'Adams', N'PA', N'17325', 0, 0); GO
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors1";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT ALL * FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>Address</td>
<td>City</td>
<td>Locality</td>
<td>County</td>
<td style="text-align: center">State</td>
<td>ZIP-Code</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: center">Stories</td>
<td style="text-align: center">Year Built</td>
<td style="text-align: right">Market Value</td>
</tr>
@while (sdrProperties.Read())
{
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@sdrProperties[0]</td>
<td>@sdrProperties[1]</td>
<td>@sdrProperties[2]</td>
<td>@sdrProperties[3]</td>
<td>@sdrProperties[4]</td>
<td style="text-align: center">@sdrProperties[5]</td>
<td>@sdrProperties[6]</td>
<td>@Html.DropDownList("PropertyType", @sdrProperties[7].ToString(), @PropertyType, new { style = "background-color: antiquewhite; color: black;" })</td>
<td>@Html.DropDownList("Condition", @sdrProperties[8].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@sdrProperties[9]</td>
<td style="text-align: center">@sdrProperties[10]</td>
@if (sdrProperties[11].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (sdrProperties[12].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: center">@sdrProperties[13]</td>
<td style="text-align: center">@sdrProperties[14]</td>
<td style="text-align: right">@sdrProperties[15]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>
Sorting the Records in SQL
To let you sort records, the SQL provides the ORDER BY expression. The formula to follow is:
SELECT what FROM what-object ORDER BY column-name;
The column used as the basis must be recognized as part of the selected columns.
Practical Learning: Sorting the Records in SQL
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, Locality, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, MarketValue " +
"FROM RealEstate.Properties " +
"ORDER BY PropertyNumber;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>Locality</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: right">Market Value</td>
</tr>
@while (sdrProperties.Read())
{
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@sdrProperties[0]</td>
<td>@sdrProperties[1]</td>
<td>@sdrProperties[2]</td>
<td style="text-align: center">@sdrProperties[3]</td>
<td>@Html.DropDownList("PropertyType", @sdrProperties[4].ToString(), @PropertyType, new { style = "background-color: antiquewhite; color: black;" })</td>
<td>@Html.DropDownList("Condition", @sdrProperties[5].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@sdrProperties[6]</td>
<td style="text-align: center">@sdrProperties[7]</td>
@if (sdrProperties[8].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (sdrProperties[9].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@sdrProperties[10]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>
Sorting Records in Ascending Order
By default, records are ordered in ascending order based on the column that is chosen. Nevertheless, to let you indicate that the records must be ordered in ascending order, the SQL provides the ASC keyword. It is used after the based field. Here is an example of using it:
SELECT ALL * FROM RealEstate.Properties
ORDER BY City ASC
Sorting Records in Descending Order
Sorting records in reverse order is referred to as sorting in descending order. To support this, the SQL provides the DESC keyword. It produces the opposite result to the ASC effect. Here is an example:
@{
ViewBag.Title = "Tickets Analysis";
}
<h2 class="text-center">Tickets Analysis</h2>
<div align="center">
@{
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
{
string strTrafficSystem = "SELECT TicketNumber, CameraNumber, VehicleTagNumber, " +
" ViolationCategory, ViolationLocation " +
"FROM CitationDivision.CamerasTickets " +
"ORDER BY ViolationCategory DESC;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
<table border="6">
<tr style="background-color: #4f0505; color: antiquewhite;">
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td><b>Camera #</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td><b>Violation</b></td>
<td><b>Location</b></td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr style="background-color: ivory; color: black;">
<td>@sdrTrafficSystem[0].ToString()</td>
<td>@sdrTrafficSystem[1].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[2].ToString()</td>
<td>@sdrTrafficSystem[3].ToString()</td>
<td>@sdrTrafficSystem[4].ToString()</td>
</tr>
}
</table>
}
}
</div>
This would produce:
Sorting Records Based on Type
Null Fields
We already know that some fields can hold a value or be null, which would indicate that the field has no value. As mentioned already, to sort records, you must specify the column by which you are sorting. If some records of that field are null, those records would be selected first.
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 text (char, varchar, and their variants nchar and nvarchar), 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:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, Locality, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, MarketValue " +
"FROM RealEstate.Properties " +
"ORDER BY City;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>Locality</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: right">Market Value</td>
</tr>
@while (sdrProperties.Read())
{
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@sdrProperties[0]</td>
<td>@sdrProperties[1]</td>
<td>@sdrProperties[2]</td>
<td style="text-align: center">@sdrProperties[3]</td>
<td>@Html.DropDownList("PropertyType", @sdrProperties[4].ToString(), @PropertyType, new { style = "background-color: antiquewhite; color: black;" })</td>
<td>@Html.DropDownList("Condition", @sdrProperties[5].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@sdrProperties[6]</td>
<td style="text-align: center">@sdrProperties[7]</td>
@if (sdrProperties[8].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (sdrProperties[9].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@sdrProperties[10]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>
This would produce:
If the order-based column has null values, their records would come first. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, Locality, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, MarketValue " +
"FROM RealEstate.Properties " +
"ORDER BY State;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>Locality</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: right">Market Value</td>
</tr>
@while (sdrProperties.Read())
{
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@sdrProperties[0]</td>
<td>@sdrProperties[1]</td>
<td>@sdrProperties[2]</td>
<td style="text-align: center">@sdrProperties[3]</td>
<td>@Html.DropDownList("PropertyType", @sdrProperties[4].ToString(), @PropertyType, new { style = "background-color: antiquewhite; color: black;" })</td>
<td>@Html.DropDownList("Condition", @sdrProperties[5].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@sdrProperties[6]</td>
<td style="text-align: center">@sdrProperties[7]</td>
@if (sdrProperties[8].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (sdrProperties[9].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@sdrProperties[10]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>This would produce:
Remember that you can add the ASC keyword to re-enforce the idea that you want to sort the records in ascending order. Also, remember that, to reverse an ascending arrangement, add the DESC keyword after the name of the column.
Sorting Boolean Fields
Boolean fields are those that use 0 (false) and 1 (true) values. If you arrange a list based on such a field, the 0 (false) records would come first followed by records with a 1 (or true) value. Here is an example:
@{
ViewBag.Title = "Tickets Analysis";
}
<h2 class="text-center">Tickets Analysis</h2>
<div align="center">
@{
bool availablePhoto = false;
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
{
string strTrafficSystem = "SELECT TicketNumber, CameraNumber, VehicleTagNumber, " +
" ViolationCategory, ViolationLocation, PhotoAvailable " +
"FROM CitationDivision.CamerasTickets " +
"ORDER BY PhotoAvailable ASC;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
<table border="6">
<tr style="background-color: #4f0505; color: antiquewhite;">
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td><b>Camera #</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td><b>Violation</b></td>
<td><b>Location</b></td>
<td><b>Photo Available?</b></td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr style="background-color: ivory; color: black;">
<td>@sdrTrafficSystem[0].ToString()</td>
<td>@sdrTrafficSystem[1].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[2].ToString()</td>
<td>@sdrTrafficSystem[3].ToString()</td>
<td>@sdrTrafficSystem[4].ToString()</td>
@if (sdrTrafficSystem[5].ToString().Equals("True"))
{
availablePhoto = true;
}
else
{
availablePhoto = false;
}
<td style="text-align: center">@Html.CheckBox("PhotoAvailable", availablePhoto)</td>
</tr>
}
</table>
}
}
</div>
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).
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.
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:
SELECT PropertyNumber,
City,
Locality,
[State],
PropertyType,
Condition,
Bedrooms,
Bathrooms,
FinishedBasement,
IndoorGarage,
MarketValue
FROM RealEstate.Properties
ORDER BY PropertyNumber ASC;
Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.
Date and Time-Based Fields
The SQL supports date, time, and combinations of date and time values. 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:
@{
ViewBag.Title = "Tickets Analysis";
}
<h2 class="text-center">Tickets Analysis</h2>
<div align="center">
@{
bool availablePhoto = false;
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
{
string strTrafficSystem = "SELECT TicketNumber, ViolationDate, CameraNumber, VehicleTagNumber, ViolationCategory, " +
" ViolationLocation, PhotoAvailable " +
"FROM CitationDivision.CamerasTickets " +
"ORDER BY ViolationDate ASC;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
<table border="6">
<tr style="background-color: #4f0505; color: antiquewhite;">
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td><b>Violation Date</b></td>
<td><b>Camera #</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td><b>Violation</b></td>
<td><b>Location</b></td>
<td><b>Photo Available?</b></td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr style="background-color: ivory; color: black;">
<td>@sdrTrafficSystem[0].ToString()</td>
<td style="text-align: center; background-color: tomato; color:antiquewhite">@DateTime.Parse(sdrTrafficSystem[1].ToString()).ToShortDateString()</td>
<td>@sdrTrafficSystem[2].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[3].ToString()</td>
<td>@sdrTrafficSystem[4].ToString()</td>
<td>@sdrTrafficSystem[5].ToString()</td>
@if (sdrTrafficSystem[6].ToString().Equals("True"))
{
availablePhoto = true;
}
else
{
availablePhoto = false;
}
<td style="text-align: center">@Html.CheckBox("PhotoAvailable", availablePhoto)</td>
</tr>
}
</table>
}
}
</div>
This would produce
@{
ViewBag.Title = "County Police - Tickets Analysis";
}
<h2 style="text-align: center">County Police - Tickets Analysis</h2>
<div align="center">
@{
bool availablePhoto = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "TrafficTicketSystem";
scsb.DataSource = "(Local)";
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strTrafficSystem = "SELECT TicketNumber, ViolationTime, CameraNumber, VehicleTagNumber, ViolationCategory, " +
" ViolationLocation, PhotoAvailable " +
"FROM CitationDivision.CamerasTickets " +
"ORDER BY ViolationTime ASC;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
<table border="6">
<tr style="background-color: #4f0505; color: antiquewhite;">
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td style="background-color: brown; color: blanchedalmond"><b>Violation Time</b></td>
<td><b>Camera #</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td><b>Violation</b></td>
<td><b>Location</b></td>
<td><b>Photo Available?</b></td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr style="background-color: ivory; color: black;">
<td>@sdrTrafficSystem[0].ToString()</td>
<td style="text-align: center; background-color: brown; color: blanchedalmond">@sdrTrafficSystem[1].ToString()</td>
<td>@sdrTrafficSystem[2].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[3].ToString()</td>
<td>@sdrTrafficSystem[4].ToString()</td>
<td>@sdrTrafficSystem[5].ToString()</td>
@if(sdrTrafficSystem[6].ToString().Equals("True"))
{
availablePhoto = true;
}
else
{
availablePhoto = false;
}
<td style="text-align: center">@Html.CheckBox("PhotoAvailable", availablePhoto)</td>
</tr>
}
</table>
}
}
</div>
,
CameraNumber,
VehicleTagNumber,
ViolationCategory,
ViolationLocation,
PhotoAvailable
FROM CitationDivision.CamerasTickets
ASC;
This would produce:
@{
ViewBag.Title = "County Police - Tickets Analysis";
}
<h2 style="text-align: center">County Police - Tickets Analysis</h2>
<div align="center">
@{
bool availablePhoto = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "TrafficTicketSystem";
scsb.DataSource = "(Local)";
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strTrafficSystem = "SELECT TicketNumber, CameraNumber, VehicleTagNumber, ViolationCategory, " +
" ViolationLocation, MediaTransferDate, PhotoAvailable " +
"FROM CitationDivision.CamerasTickets " +
"ORDER BY MediaTransferDate ASC;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
<table border="6">
<tr style="background-color: #4f0505; color: antiquewhite;">
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td><b>Camera #</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td><b>Violation</b></td>
<td><b>Location</b></td>
<td style="background-color: brown; color: blanchedalmond"><b>Date/Time Media Was Transfered</b></td>
<td><b>Photo Available?</b></td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr style="background-color: ivory; color: black;">
<td>@sdrTrafficSystem[0].ToString()</td>
<td>@sdrTrafficSystem[1].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[2].ToString()</td>
<td>@sdrTrafficSystem[3].ToString()</td>
<td>@sdrTrafficSystem[4].ToString()</td>
<td style="text-align: center; background-color: brown; color: blanchedalmond">@sdrTrafficSystem[5].ToString()</td>
@if(sdrTrafficSystem[6].ToString().Equals("True"))
{
availablePhoto = true;
}
else
{
availablePhoto = false;
}
<td style="text-align: center">@Html.CheckBox("PhotoAvailable", availablePhoto)</td>
</tr>
}
</table>
}
}
</div>
Sorting More Than One Column
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 records using more than one column using the SQL, after the ORDER BY expression, 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.
Sorting Two String-Based Columns
Imagine you have two string-based records that have the same value. If you sort them, the primary column would display the similar records first. The additional column would order the dissimilar records inside each group. Here is an example:
<!DOCTYPE html>
<html>
<head>
<title>Altair Realtors - Properties Listing</title>
</head>
<body>
<div align="center">
<h1>Altair Realtors - Properties Listing</h1>
@{
bool hasIndoorGarage = false;
bool basementIsFinished = false;
System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder();
scsb.IntegratedSecurity = true;
scsb.InitialCatalog = "AltairRealtors";
scsb.DataSource = "(Local)";
List<SelectListItem> PropertyType = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT PropertyType " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
PropertyType.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
List<SelectListItem> Condition = new List<SelectListItem>();
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT DISTINCT Condition " +
"FROM RealEstate.Properties;";
System.Data.SqlClient.SqlCommand cmdProperties = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdProperties.ExecuteReader();
while (sdrProperties.Read())
{
Condition.Add(new SelectListItem { Value = @sdrProperties[0].ToString(), Text = @sdrProperties[0].ToString() });
}
}
using (System.Data.SqlClient.SqlConnection scAltairRealtors = new System.Data.SqlClient.SqlConnection(scsb.ConnectionString))
{
string strProperties = "SELECT PropertyNumber, City, Locality, [State], PropertyType, Condition, " +
" Bedrooms, Bathrooms, FinishedBasement, IndoorGarage, MarketValue " +
"FROM RealEstate.Properties " +
"ORDER BY City, Locality;";
System.Data.SqlClient.SqlCommand cmdSelection = new System.Data.SqlClient.SqlCommand(strProperties, scAltairRealtors);
scAltairRealtors.Open();
System.Data.SqlClient.SqlDataReader sdrProperties = cmdSelection.ExecuteReader();
<table border="6">
<tr style="font-weight: 600; background-color: maroon; color: antiquewhite">
<td style="text-align: center">Property #</td>
<td>City</td>
<td>Locality</td>
<td style="text-align: center">State</td>
<td>Property Type</td>
<td>Condition</td>
<td style="text-align: center">Beds</td>
<td style="text-align: center">Baths</td>
<td style="text-align: center">Finished Basement?</td>
<td style="text-align: center">Indoor Garage?</td>
<td style="text-align: right">Market Value</td>
</tr>
@while (sdrProperties.Read())
{
<tr style="background-color: antiquewhite; color: black;">
<td style="text-align: center">@sdrProperties[0]</td>
<td>@sdrProperties[1]</td>
<td>@sdrProperties[2]</td>
<td style="text-align: center">@sdrProperties[3]</td>
<td>@Html.DropDownList("PropertyType", @sdrProperties[4].ToString(), @PropertyType, new { style = "background-color: antiquewhite; color: black;" })</td>
<td>@Html.DropDownList("Condition", @sdrProperties[5].ToString(), @Condition, new { style = "background-color: antiquewhite; color: black;" })</td>
<td style="text-align: center">@sdrProperties[6]</td>
<td style="text-align: center">@sdrProperties[7]</td>
@if (sdrProperties[8].ToString() == "True")
{
basementIsFinished = true;
}
else
{
basementIsFinished = false;
}
<td style="text-align: center">@Html.CheckBox("chkFinishedBasement", @basementIsFinished)</td>
@if (sdrProperties[9].ToString() == "True")
{
hasIndoorGarage = true;
}
else
{
hasIndoorGarage = false;
}
<td style="text-align: center">@Html.CheckBox("chkIndoorGarage", @hasIndoorGarage)</td>
<td style="text-align: right">@sdrProperties[10]</td>
</tr>
}
</table>
}
}
</div>
</body>
</html>
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 Date/Time-Based Columns
As you may know already, Transact-SQL supports records that use date only, time only, or a combination of date and time values. When sorting the records, you can combine the fields to get a certain arrangement 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 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:
@{
ViewBag.Title = "Tickets Analysis";
}
<h2 class="text-center">Tickets Analysis</h2>
<div align="center">
@{
bool availablePhoto = false;
using (System.Data.SqlClient.SqlConnection scTrafficSystem = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["csTrafficTicketsManagement"].ConnectionString))
{
string strTrafficSystem = "SELECT TicketNumber, CameraNumber, ViolationDate, ViolationTime, VehicleTagNumber, ViolationCategory, " +
" ViolationLocation, PhotoAvailable " +
"FROM CitationDivision.CamerasTickets " +
"ORDER BY ViolationDate, ViolationTime ASC;";
System.Data.SqlClient.SqlCommand cmdTrafficSystem = new System.Data.SqlClient.SqlCommand(strTrafficSystem, scTrafficSystem);
scTrafficSystem.Open();
System.Data.SqlClient.SqlDataReader sdrTrafficSystem = cmdTrafficSystem.ExecuteReader();
<table border="6">
<tr style="background-color: #4f0505; color: antiquewhite;">
<td style="text-align: center; font-weight: 600">Ticket #</td>
<td><b>Camera #</b></td>
<td style="background-color: brown; color: blanchedalmond"><b>Violation Date</b></td>
<td style="background-color: brown; color: blanchedalmond"><b>Violation Time</b></td>
<td style="text-align: center; font-weight: 600">Tag Number</td>
<td><b>Violation</b></td>
<td><b>Location</b></td>
<td><b>Photo Available?</b></td>
</tr>
@while (sdrTrafficSystem.Read())
{
<tr style="background-color: ivory; color: black;">
<td>@sdrTrafficSystem[0].ToString()</td>
<td style="text-align: center">@sdrTrafficSystem[1].ToString()</td>
<td style="text-align: center; background-color: brown; color: blanchedalmond">@DateTime.Parse(sdrTrafficSystem[2].ToString()).ToShortDateString()</td>
<td style="text-align: center; background-color: brown; color: blanchedalmond">@sdrTrafficSystem[3].ToString()</td>
<td>@sdrTrafficSystem[4].ToString()</td>
<td>@sdrTrafficSystem[5].ToString()</td>
<td>@sdrTrafficSystem[6].ToString()</td>
@if (sdrTrafficSystem[7].ToString().Equals("True"))
{
availablePhoto = true;
}
else
{
availablePhoto = false;
}
<td style="text-align: center">@Html.CheckBox("PhotoAvailable", availablePhoto)</td>
</tr>
}
</table>
}
}
</div>
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:
Practical Learning: Ending the Lesson
|
|
||
| Previous | Copyright © 2001-2021, FunctionX | Next |
|
|
||