Home

Sorting Records

 

Fundamentals of Sorting Records

 

Introduction

The list of records we get with a SELECT statement is presented in the order the records appear 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 arrangement from one specific field.

Practical LearningPractical Learning: Introducing Sorting Records

  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. In the Object Explorer, right-click the name of the server and click New Query
  4. In the Object Explorer, expand Databases
  5. From the previous lesson, make sure you have the Department Store1 database.
    In the Object Explorer, expand DepartmentStore1 and expand Tables
  6. Right-click Inventory.StoreItems and click Edit Top 200 Rows
  7. On the Query Designer toolbar, click the Show Diagram Pane button Show Diagram Pane, the Show Criteria Pane button Show Criteria Pane, and the Show SQL Pane button Show SQL Pane
  8. In the Diagram pane, remove the check boxes of all fields
  9. In the SQL pane, delete TOP (200)
  10. In the Diagram pane, click the check boxes of ItemNumber (you may receive a message box; read it and click OK, then click the ItemNumber check box again), ItemName, and UnitPrice
  11. On the main menu, click Query Designer -> Execute SQL
Introducing Sorting Records

Visually Sorting Records

To get an alphabetical or an incremental order of records, you must let the database engine know what field would be used as reference. To visually specify the order, if you are using a Query Editor or the Query Designer:

  • In the Diagram pane, right-click a field and select either Sort Ascending or Sort Descending
  • In the Criteria pane, under the Sort Type column, click the corresponding box of the desired column. This would reveal that it is a combo box. Click the arrow of that combo box and make your selection:

Using the Table Window

Practical LearningPractical Learning: Visually Sorting Records

  1. In the Criteria pane, under Column, click ItemName and press Tab 4 times
  2. Type a and press Tab. Make sure it displays Ascending

Sorting Records in the SQL

In SQL, to specify the sorting order, add the ORDER BY expression after the name of the table. The formula used would be:

SELECT WhatField(s) FROM WhatObject 
ORDER BY WhatField;
Sorting Records

The column used as the basis must be recognized as part of the selected columns.

The SQL supports two orders of sorting: ascending or descending orders. If you arrange the records in SQL and use ORDER BY followed only by the name of the column, the records would be arranged in ascending order, which is the default. Otherwise, to explicitly specify that you want to arrange the records in ascending order, add the ASC keyword after the name of the column. The formula to use is:

SELECT WhatField(s) FROM WhatObject ORDER BY WhatField ASC;

If you are working in the Query Designer, to arrange the records in ascending order:

  • In the Diagram pane, right-click the desired field and click Sort Ascending
  • In the Criteria pane, after selecting a column, click the box at the intersection of that column and Sort Type, and select Ascending

The reverse of arranging the records normally is the descending order. If you are working in the Query Designer, to arrange the records in ascending order:

  • In the Diagram pane, right-click the desired field and click Sort Descending
  • In the Criteria pane, after identifyng the desired column, click the box at the intersection of that column and Sort Type, and select Descending

If you are arranging the records using code, you must explicitly indicate that you want to arranging in descending order. This is done by adding the DESC keyword after the name of the column. The formula to use is:

SELECT WhatField(s) FROM WhatObject ORDER BY WhatField DESC;

As we will see in the next sections, when sorting the records, the result you will get depend on the (data) type of (the) column.

Practical LearningPractical Learning: Sorting Records in the SQL

  1. In the SQL pane, observe the statement as it includes ORDER BY.
    On the main menu, click Query Designer -> Execute SQL
     
    Sorting Records
  2. In the Criteria pane, click Column to select all fields and press Delete

Sorting Records Based on Type

 

Sorting Null Fields

We already know that some fields can hold a value or be null, which would indicate that such a field has no value. As mentioned already, to sort records, you must specify the column by which you are sorting. If some records of that field are null, those records would be selected first. Here is an example:

SELECT * FROM Registration.Students
ORDER BY LastName;
GO

This would produce:

Sorting Records

On the other hand, if you sort the records in descending order, the non-null records would come first.

Practical LearningPractical Learning: Sorting NULL Fields

  1. In the Diagram pane, click the check boxes of ItemNumber, Manufacturer, and UnitPrice
  2. In the Criteria pane, click the box at the intersection of Manufacturer and Sort Type. Select Ascending
  3. On the main menu, click Query Designer -> Execute SQL
  4. In the Results pane, in the Manufacturer column, notice that the records with NULL come up first. Scroll down to see the non-NULL fields:
     
    Sorting Records

Sorting String-Based Fields

If you sort the records based on a column that uses plain text (char, varchar, varchar(max) and their variants nchar, nvarchar, and nvarchar(max)), the database engine would refer to the language used by the database. If the language is latin-based, which is the default in US English, the records would be arranged in alphabetical order based on the indicated column.

Here is an example that gives a list of students based on the students numbers:

SELECT StudentNumber [Student #],
       FirstName [First Name], 
       LastName [Last Name], 
       Gender,
       ParentsNames [Parents Names],
       SingleParentHome "SPH?"
FROM   Registration.Students
ORDER BY StudentNumber;
GO

This would produce:

Sorting Records

As mentioned already, if the column has null values, their records would come first. Also, you can add the ASC keyword to re-enforce the idea that you want to sort the records in ascending order.

To reverse an ascending arrangement, add the DESC keyword after the name of the column. Here is an example:

SELECT FirstName,
       LastName,
       Gender,
       ParentsNames,
       SingleParentHome
FROM   Registration.Students
ORDER BY LastName DESC;
GO

This would produce:

Sorting Records

Practical LearningPractical Learning: Sorting String-Based Fields

  1. In the Criteria pane, click Manufacturer. Then click the arrow of its combo box and select ItemName
  2. Right-click inside the Diagram pane and click Execute SQL:
     
    Sorting Records

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 NULL records would come first, followed by records with a 0 value, and followed by records with 1. Here is an example:

USE Exercise;
GO
CREATE TABLE Videos(Title nvarchar(50), [Length] int,
	Rating nchar(10), [Year] int, WideScreen bit);
GO
INSERT INTO Videos
VALUES(N'Last Castle (The)', 133, N'R', 2001, 1);
GO
INSERT INTO Videos(Title, [Length], [Year])
VALUES(N'Sex, Lies, and Videotape', 99, 1989);
GO
INSERT INTO Videos(Title, [Length], [Year], WideScreen)
VALUES(N'American President (The)', 115, 1995, 0);
GO
INSERT INTO Videos(Title, WideScreen, Rating)
VALUES(N'Day After Tomorrow (The)', 1, N'PG-13');
GO
INSERT INTO Videos(Title, [Length], Rating, WideScreen)
VALUES(N'Sneakers', 126, N'PG-13', 1);
GO

SELECT * FROM Videos
ORDER BY WideScreen;
GO

This would produce:

Sorting Records

If you sort the records in descending order, the records with 1 value would come up first, followed by those with 0, and then the NULL values.

Practical LearningPractical Learning: Sorting Boolean Fields

  1. Click the SQLQuery1.sql tab and change the statement as follows:
    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    ORDER BY aparts.Available;
    GO
  2. To execute, press F5

Sorting Number-Based Fields

As you may know already, the SQL supports various types of numeric values. The columns that use those values can be sorted in incremental order. The SQL interpreter uses the rules specified in the Control Panel. For example, in US English, the referenced number is 0. Then there are negative and positive values. Of course, negative values come before 0 and positive values come after.

As seen with other types, if you sort the records based on a number-based column, if that column has null records, those records would come first. The other records would be sorted in increment order. Here is an example:

SELECT Title, Director, Rating, YearReleased "(c) Year"
FROM Videos
ORDER BY YearReleased;
GO

This would produce:

Sorting Records

Of course, to sort the records in decrementing order, apply the DESC keyword after the name of the column.

Practical LearningPractical Learning: Sorting Number-Based Fields

  1. Click the SQLQuery1.sql tab and type the following (the LambdaSquare1 database was created in Lesson 10):
    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    ORDER BY aparts.Price;
    GO
  2. Press F5 to execute
  3. Change the statement as follows:
    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    ORDER BY "Due Before Moving";
    GO
  4. Press F5 to execute
  5. Click the other tab
  6. In the Criteria pane, click ItemName. Then press Tab four times to select Ascending and press Delete
  7. Press the down arrow key and type a. Make sure Ascending has been selected for the UnitPrice field
  8. On the main menu, click Query Designer -> Execute SQL:
     
    Sorting Records
  9. In the Criteria pane, click Column to select all columns
  10. Press Delete
  11. In the Diagram pane, click the check boxes of ItemNumber, Manufacturer, ItemName, and UnitPrice
  12. In the Criteria pane, click ItemNumber, press Tab and type Item #
  13. In the Criteria pane, click ItemName, press Tab and type Name/Description
  14. In the Criteria pane, click UnitPrice, press Tab and type Unit Price

Sorting More Than One Column

 

Introduction

Consider the following table:

USE Exercise;
GO
CREATE TABLE Employees([Empl #] nchar(10), [First Name] nvarchar(20),
	[Last Name] nvarchar(20), Salary money, [Full Time?] bit);
GO
INSERT INTO Employees
VALUES(N'29730', N'Philippe', N'Addy', 20.05, 1);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'28084', N'Joan', N'Shepherd', 12.72);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'79272', N'Joshua', N'Anderson', 18.26);
GO
INSERT INTO Employees
VALUES(N'22803', N'Gregory', N'Swanson', 15.95, 0);
GO
INSERT INTO Employees([Empl #], [Last Name], Salary, [Full Time?])
VALUES(N'28084', N'Shepherd', 12.72, 1),
      (N'39742', N'Anders', 8.88, 0);
GO
INSERT INTO Employees
VALUES(N'83084', N'Josephine', N'Anderson', 20.02, 1);
GO
INSERT INTO Employees([Empl #], [First Name], [Last Name], Salary)
VALUES(N'79272', N'James', N'Anders', 18.26),
      (N'27924', N'Gregory', N'Hope', 12.85),
      (N'39742', N'John', N'Anderson', 8.88);
GO
SELECT * FROM Employees;
GO

This would produce:

Sorting Records

Imagine you want to arrange the list based on salaries, you would execute a statement as:

SELECT [Empl #], [First Name], [Last Name], [Salary],
CASE [Full Time?]
	WHEN 0 THEN N'Contractor'
	WHEN 1 THEN N'Full Time'
	ELSE N'Unspecified'
END AS [Employment Status]
FROM Employees
ORDER BY [Salary];
GO

This would produce:

Sorting Records

Notice that some records have the same salaries. If you get a situation where many records on a column have the same value, you can specify an additional column by which to sort the records.

To visually sort by more than one column, in the Criteria pane, click the Sort Type corresponding to the first column and select the desired option. To specify the subsequent column, click the box corresponding to its Sort Type and select the desired option. To keep track of the columns you are using, in the Sort Order column, the database engine would create an incrementing number for each. The first column receives the number 1, the second receives the number 2, and so on. Here is an example:

Sorting Order

If you don't like the order suggested, click the Sort Order box corresponding to the column whose position you want to change, then click the arrow of its combo box and select the desired number:

Sorting Order

After making your selection, the studio would update the order of sorting columns.

To arrange the list using more than one column using the SQL, after ORDER BY, type the columns separated by commas.

Practical LearningPractical Learning: Sorting More Than One Column

  1. Click the SQLQuery1.sql tab
  2. Change the statement as follows:
    USE LambdaSquare1;
    GO
    SELECT "Unit #" = aparts.UnitNumber,
           Beds = aparts.Bedrooms, 
           Baths = aparts.Bathrooms,
           [Monthly Rent] = aparts.Price, 
           "Primary Deposit" = aparts.Deposit,
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    ORDER BY [Monthly Rent], "Primary Deposit";
    GO
  3. Press F5 to execute

Sorting Order

Sorting Non-NULL and NULL Fields

If you specify more than one record to sort by, the database engine sorts the primary column first. Then, on the second field, when two records have the same value, the NULL values would come first. Here is an example:

SELECT [Empl #], [First Name], [Last Name], [Salary], [Full Time?]
FROM Employees
ORDER BY [Salary], [Full Time?];
GO

This would produce:

Sorting Records

Notice that when two records have the same values and if one of the records has a NULL value, that one comes first.

Sorting Two String-Based Columns

Imagine you have two string-based records that have the same value. If you sort them, you would wonder which one would come up first. An additional field would solve this problem. That is, you can combine fields to sort the records. Here is an example:

SELECT [Empl #], [First Name], [Last Name], [Salary],
CASE [Full Time?]
	WHEN 0 THEN N'No'
	WHEN 1 THEN N'Yes'
	ELSE N'Don''t Know'
END AS [Employment Status]
FROM Employees
ORDER BY [Last Name], [First Name];
GO

If you do this, the SQL interpreter would first sort the records based on the first field, in which case the records would be grouped. It is then the second field that would be used to handle the assignment. In other words, using the alphabetical order, the value that comes first would be based on the alphabet, such as US English. The above statement would produce:

Using the SQL to Sort Records

Notice that, when you sort more than one string-based fields, the records with NULL values come first, such is the case for the above Anders and the Shepherd records. For the fields that are not null, the records are sorted based on the second records; that's the case for the Anderson records.

Practical LearningPractical Learning: Sorting Two String-Based Columns

  1. Click the other tab
  2. In the Criteria pane, click the box at the intersection of Manufacturer and Sort Type
  3. Select Ascending
  4. Press the down arrow key and type a. Make sure Ascending is selected for the ItemName field
  5.  Click somewhere else in the window. Observe the statement in the SQL pane:
    SELECT ItemNumber AS [Item #], Manufacturer,
           ItemName AS [Name/Description], UnitPrice AS [Unit Price]
    FROM   Inventory.StoreItems
    ORDER BY Manufacturer, [Name/Description]
  6. Right-click somewhere in the window and click Execute SQL
  7. Notice that, in the Manufacturer column, the NULL records come first.
    Scroll down in the Results pane to locate records that have the same manufacturer. Notice that it is their Name/Description column that handles the sorting:
     
    Sorting With More Than One Column
 
 
 

Options on Sorting Records

     

Sorting by an Expression

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

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

This would produce:

Using the SQL to Sort Records

Using a Function to Sort Records

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

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

This would produce:

Using the SQL to Sort Records

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

USE DepartmentStore1;
GO

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

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

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

USE DepartmentStore1;
GO

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

Here is an example that uses the other function:

USE DepartmentStore1;
GO

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

Sorting the Records With Ties

Consider the following table and its records:

USE VideoCollection1;
GO
DROP TABLE Videos;
GO
CREATE TABLE Videos
(
	[Shelf #] nchar(7) null,
	Title nvarchar(50) not null,
	Director nvarchar(50),
	[Length] int,
	Rating nchar(10),
	[Year] int
);
GO

INSERT INTO Videos
VALUES(N'DHE-927', N'Two for the Money', N'D.J. Caruso', 123, N'R', 2008),
      (N'CGM-683', N'Her Alibi', N'Bruce Beresford', 94, N'PG-13', 1998),
      (N'FQT-973', N'Memoirs of a Geisha', N'Rob Marshall', 145, N'PG-13', 2006),
      (N'DBT-395', N'Wall Street', N'Oliver Stone', 126, N'R', 2000);
      
GO
INSERT INTO Videos(Title, Director, [Length], Rating)
VALUES(N'Stealing Harvard', N'Bruce McCulloch', 85, N'PG-13');
GO

INSERT INTO Videos([Shelf #], Title, Director, [Length], [Year])
VALUES(N'TPH-973', N'A Few Good Men', N'Rob Reiner', 138, 1992);
GO

INSERT INTO Videos(Title, Director, [Year], [Length])
VALUES(N'The Silence of the Lambs', N'Jonathan Demme', 1991, 118);
GO

INSERT INTO Videos([Shelf #], Title, Director, Rating, [Length])
VALUES(N'DZV-737', N'The Lady Killers', N'Joel Coen & Ethan Coen', N'R', 104);
GO

INSERT INTO Videos(Title, Director, [Length],  Rating, [Year])
VALUES(N'Sneakers', N'Phil Alden Robinson', 126, N'PG-13', 1992),
      (N'Annie', N'John Huston', 126, N'G', 1982),
      (N'Dave', N'Ivan Reitman', 110, N'PG-13', 1993);
GO

INSERT INTO Videos
VALUES(N'ADR-737', N'Incredibles (The)', N'Brad Bird', 133, N'PG', 2004);
GO

As we have seen so far, to get the list of all records, you would execute:

SELECT * FROM Videos;

Videos

Notice that the statement produces 12 records. To get the first 40% records, you would execute:

SELECT TOP 40 PERCENT *
FROM Videos;
GO

Videos

Notice that you get 5 records that include 3 with a PG-13 rating. If you want to arrange the list based on the Rating column, you can add the ORDER BY clause as follows:

SELECT TOP 40 PERCENT *
FROM Videos
ORDER BY Rating;
GO

Videos

Notice that you still get 5 records but this time, only one is with PG-13 and the PG-13 record is the last. Transact-SQL provides an operation that associates with the ORDER BY statement and the TOP PERCENT value. The operation works as follows:

  1. First select the top expression percent records
  2. Second, based on the column used by the ORDER BY clause, show all records that use the value of that column, even if the result will produce more records than the specified percent value

To visually perform this operation, in the Object Explorer, right-click the table or view and click Edit Top 200 Rows. Include at least either the Criteria pane or the SQL pane.

In either the Criteria pane or the SQL pane, set the order of your choice (ascending or descending) for the column that will hold the arrangement. Here is an example:

Videos

In the Properties window, expand the Top Specification field and make sure its (Top) field is set to Yes (it should be set already). In the Expression field, enter the value you want. Set the Percent field to Yes. Set the With Ties field to Yes. Here is an example:

Expression

Once these are done, you can execute the statement:

Videos

In Transact-SQL, the formula to perform the above operation is:

SELECT TOP ( expression ) [ PERCENT ] [ WITH TIES ]
What Columns
FROM Object
ORDER BY Column

The WITH TIES clause asks the SELECT statement to perform the two operations we saw above. The WITH TIES expression is entered after the PERCENT keyword, which is before the list of columns. Here is an example:

SELECT TOP 40 PERCENT WITH TIES *
FROM Videos
ORDER BY Rating;
GO

This would produce:

Videos

Consequently, the WITH TIES condition is used to select the top percent records plus all records that use the value of the last record depending on the column specified by the ORDER BY clause.

Practical LearningPractical Learning: Sorting With Ties

  1. In the Criteria pane, delete the words Ascending
  2. In the Diagram pane, click the check boxes of Categories and SubCategories
  3. In the Criteria pane, arrange the columns to have the same order as in the Diagram pane
  4. Set the Alias of SubCategories to display Sub-Categories
  5. Click the box at the intersection of Categories and Sort Type. Select Ascending
  6. If the Properties window is not displaying, on the main menu, click View -> Properties window.
    In the Properties window, click the + button of Top Specification to expand it
  7. Double-click Top to set its value to Yes
  8. Click Expression and type 20 

    Field Selection

  9. On the main menu, click Query Designer -> Execute SQL. Notice the number of records you get

    Sorting With Ties

  10. In the Properties window, double-click With Ties to set its value to Yes
  11. On the main menu, click Query Designer -> Execute SQL. Notice the new number of records you get

    Sorting With Ties

  12. Close the Query Designer

Skipping a Number of Records

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

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

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

Practical LearningPractical Learning: Skipping a Number of Records

  1. Click the SqLQuery1 tab and type the following code:
    USE LambdaSquare1;
    GO
    SELECT aparts.UnitNumber "Unit #",
           aparts.Bedrooms   Beds, 
           aparts.Bathrooms  Baths,
           aparts.Price      [Monthly Rent], 
           aparts.Deposit    "Primary Deposit",
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    ORDER BY [Monthly Rent]
    OFFSET 12 ROWS;
    GO
  2. To execute, right-click inside the Query Editor and click Execute
     
    Skipping a Number of Records

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

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

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

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

Practical LearningPractical Learning: Sorting the First Set of Records

  1. In the Query Editor, change the statement as follows:
    USE LambdaSquare1;
    GO
    SELECT aparts.UnitNumber "Unit #",
           aparts.Bedrooms   Beds, 
           aparts.Bathrooms  Baths,
           aparts.Price      [Monthly Rent], 
           aparts.Deposit    "Primary Deposit",
           (aparts.Price + aparts.Deposit) "Due Before Moving", 
           aparts.Available
    FROM Presentation.Units aparts
    ORDER BY [Monthly Rent]
    OFFSET 12 ROWS
    FETCH FIRST 10 ROWS ONLY;
    GO
  2. Right-click inside the Query Editor and click Execute
     
    Sorting the First Set of Records
  3. Close Microsoft SQL Server
  4. If asked whether you want to save, click No
 
 
   
 

Previous Copyright © 2008-2013 FunctionX Next