Lesson 4	Using ORDER BY
Objective	Sort results using ORDER BY.

Sort Results Using "Order By" in SQL-Server

The basic SELECT statement usually returns rows in the same order as they appear in the table. Most of the time you will probably want to output results arranged in a specific order. The ORDER BY clause is used to sort the results by one or more columns. Columns are sorted in ascending or descending order.

Is it a row or is it a record?
Row is a more accurate term. Some databases use the term record to describe a row, but they do have slightly different meanings. However, the terms are often used interchangeably. To alter data in a table, you use UPDATE table_name. Specify which values you want to change with SET field_name = value, and add a WHERE condition to limit the table rows updated.

Comparing Database Terms

Development Style	List of Common Items	Item in the List	Piece of Information in the List
Legacy software	File	Record	Field
Spreadsheet	Spreadsheet/worksheet/named range	Row	Column/cell
Relational algebra/logical design	Entity, or relation	Tuple (rhymes with couple)	Attribute
SQL DDL design	Table	Row	Column
Object-oriented design	Class	Object instance	Property

SQL Server developers generally refer to database elements as tables, rows, and columns when discussing the SQL Data Defi nition Language (DDL) layer or physical schema and sometimes use the terms entity, tuple, and attribute when discussing the logical design. The rest of this book uses the SQL terms (table, row, and column), but this chapter is devoted to the theory behind the design, so the relational algebra terms (entity, tuple, and attribute) are also used. A table row is a collection of columns that comprise an information unit. A record is a representation of an object. The specifics of the distinction between rows and records are best left to the theorists. It can be confusing though. For example, query results are stored in a recordset object, implying it is storing a set of records. However, it's really storing a result set, which by its definition, represents all the rows and columns resulting from a query. The following is right out of Microsoft documentation on the recordset object: "As its name implies, the Recordset object has features that you can use, depending on your query constraints, for retrieving and displaying a set of database rows, or records." For practical purposes, they're the same thing.

Columns are sorted in ascending order by default in SQL-Server

Note: unless otherwise specified, columns will be sorted in ascending order by default. Suppose you want to sort the VirtualBookShelf.com BookTable by title. The following SELECT statement does just that and returns the columns in ascending order:

SELECT * FROM BookTable 
ORDER BY Author

Notice that the ORDER BY clause comes at the end of a query.
In SQL Server 2022", columns in the result set will be sorted in "ascending order" by default when you use the `ORDER BY` clause without specifying a sort direction.
Explanation: When you use the `ORDER BY` clause and do not specify `ASC` (ascending) or `DESC` (descending), "ascending order" is the default behavior in SQL Server.
Example:

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName;

The above query will sort the `LastName` column in "ascending order" by default, equivalent to:

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName ASC;

Ascending Order:
- Numerical values: Sorted from smallest to largest.
- Text: Alphabetical order (case-insensitive unless a specific collation is used).
- Dates: Oldest to newest.
Descending Order:
- You must explicitly use the DESC keyword in the ORDER BY clause to sort in descending order.

The following series of images shows this statement in action:

1)
SELECT * FROM BookTable
This SELECT statement returns the table in the same order as the rows that appear in the table.

2) The results look like this in the Web browser. Notice the records appear in no specific order other than how they appear in the table.

3)
SELECT * FROM BookTable ORDER BY Author

This statement uses the ORDER BY clause to sort the rows by the author's name

The table is displayed in ascending order by the author's name. — 4)Now the table is displayed in ascending order by the author's name

5) The ORDER BY clause will also sort multiple columns. To do this, simply separate the column names by a comma. The ORDER BY clause in this statement specifies that the results be sorted first by Author, then by Title.

SELECT * FROM BookTable ORDER By Author, Title

6) Here are the results displayed in the browser.

7) In some cases you might want to sort the output in descending order. To do this, add the expression DESC just after the column name. Even though ascending order is the default, you can specify ascending order in the ORDER BY clause by adding ASC after the column name. Here is the unsorted BookTable.

8) The ORDER BY clause instructs the statement to sort the Author colum in ascending order and the Title column in descending order. The use of ASC is optional because ascending is the default sort order.

SELECT * FROM BookTable ORDER BY Author ASC, Title DESC

ORDER BY Clause (Transact-SQL)

Sorts data returned by a query in SQL Server. Use this clause to:

Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
Determine the order in which ranking function values are applied to the result set.

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]

<offset_fetch> ::=
{ 
  OFFSET { integer_constant | 
	offset_row_count_expression } { ROW | ROWS }
  [
   FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
  ]
}

The syntax for the SQL `ORDER BY`adheres to modern SQL standards. Below is an explanation of its components:
Syntax Breakdown

ORDER BY order_by_expression
Specifies the column or expression by which to sort the query results.
[ COLLATE collation_name ]
Specifies a collation for the sorting if the column is of a character data type.
[ ASC | DESC ]
Specifies the sort direction:
- ASC for ascending (default).
- DESC for descending.
[ , ... n ]
Allows sorting by multiple columns or expressions, separated by commas.
<offset_fetch> Clause
Provides advanced pagination capabilities:
- OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
  Specifies the number of rows to skip before starting to return rows.
  - ROW is equivalent to ROWS.
- FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
  Specifies the number of rows to return after the offset.
  - FIRST and NEXT are interchangeable.
  - ONLY is mandatory to indicate that only the specified number of rows will be returned.

Example Query

SELECT column1, column2
FROM table_name
ORDER BY column1 COLLATE Latin1_General_CI_AS DESC, column2 ASC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

Orders the rows based on column1 (descending) and column2 (ascending).
Skips the first 10 rows.
Fetches the next 5 rows.

Key Notes

The OFFSET and FETCH clauses are supported in SQL:2008 standard and later, as well as in most modern RDBMS like SQL Server, PostgreSQL, and Oracle.
If OFFSET is not specified, it defaults to OFFSET 0 ROWS.
COLLATE is optional and applies only to string sorting based on collation rules.

Your syntax matches the SQL standard and should work as intended in any compliant RDBMS.

Order By Clause - Exercise

Click the Exercise link below to practice using the ORDER BY clause.
Order By Clause - Exercise
In the next lesson, you will modify rows using the UPDATE statement.