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 LearningPractical Learning: Introducing Sorting Records

  1. Start Microsoft SQL Server (if you don't have Microsoft SQL Server, skip the Practical Learning sections of this lesson) and login/connect to the database
  2. In the Object Explorer, right-click the computer name and click New Query
  3. Type the following code:
    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
  4. To execute, on the SQL Editor toolbar, click the Execute button Execute
  5. Start Microsoft Visual Studio
  6. To start a new project, on the main menu of Microsoft Visual Studio, click File -> New -> Project ...
  7. In the New Project dialog box, make sure ASP.NET Web Application (.NET Framework) is selected.
    Change the Name to AltairRealtors1
  8. Click OK
  9. In the dialog box, click the Empty Icon and click OK
  10. In the Solution Explorer, right-click AltairRealtors1 -> Add -> New Item...
  11. In the left frame of the dialog box, expand Web and click Razor
  12. In the middle list, click Web Page (Razor v3)
  13. Change the Name to Index
  14. Click Add
  15. Change the code as follows:
    <!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>
  16. To execute the application, on the main menu, click Debug -> Start Without Debugging:

  17. Close the browser and return to your programming environment

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 LearningPractical Learning: Sorting the Records in SQL

  1. Change the document as follows:
    <!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>
  2. To execute, on the main menu, click Debug -> Start Without Debugging:

  3. Close the browser and return to your programming environment

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:

Geometry - Pentagon

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:

Sorting Records

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

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:

Sorting Records

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:

Using the SQL to Sort Records

Practical LearningPractical Learning: Ending the Lesson


Previous Copyright © 2001-2019, FunctionX Next