ORDER BY Clause – SQL Server

The ORDER BY clause defines the particular order of the rows in the result of a query. This clause has the following syntax:

ORDER BY {[col_name | col_number [ASC | DESC]]} , ...Code language: SQL (Structured Query Language) (sql)

The col_name column defines the order. col_number is an alternative specification that identifies the column by its ordinal position in the sequence of all columns in the SELECT list (1 for the first column, 2 for the second one, and so on). ASC indicates ascending order and DESC indicates descending order, with ASC as the default value.

As the syntax of the ORDER BY clause shows, the order criterion may contain more than one column, as shown in Example 6.35.

Get department numbers and employee names for employees with employee numbers < 20000, in ascending order of last and first names:

USE sample;
SELECT emp_fname, emp_lname, dept_no
	FROM employee
	WHERE emp_no < 20000
	ORDER BY emp_lname, emp_fname;Code language: SQL (Structured Query Language) (sql)

The result is

It is also possible to identify the columns in the ORDER BY clause by the ordinal position of the column in the SELECT list. The ORDER BY clause in Example 6.35 could be written in the following form:

ORDER BY 2,1Code language: SQL (Structured Query Language) (sql)

The use of column numbers instead of column names is an alternative solution if the order criterion contains any aggregate function. (The other way is to use column headings, which then appear in the ORDER BY clause.) However, using column names rather than numbers in the ORDER BY clause is recommended, to reduce the difficulty of maintaining the query if any columns need to be added or deleted from the SELECT list. Example 6.36 shows the use of column numbers.

For each project number, get the project number and the number of all employees, in descending order of the employee number:

USE sample;
SELECT project_no, COUNT(*) emp_quantity
	FROM works_on
	GROUP BY project_no
	ORDER BY 2 DESCCode language: SQL (Structured Query Language) (sql)

The result is

The Transact-SQL language orders NULL values at the beginning of all values if the order is ascending and orders them at the end of all values if the order is descending.

Using ORDER BY to Support Paging

If you want to display rows on the current page, you can either implement that in your application or instruct the database server to do it. In the former case, all rows from the database are sent to the application, and the application’s task is to retrieve the rows needed for printing and to display them. In the latter case, only those rows needed for the current page are selected from the server side and displayed. As you might guess, server-side paging generally provides better performance, because only the rows needed for printing are sent to the client.

SQL Server 2012 introduces two new clauses of the SELECT statement, OFFSET and FETCH, to support server-side paging. Example 6.37 shows the use of these two clauses.

Get the business entity ID, job title, and birthday for all female employees from the AdventureWorks database in ascending order of job title. Display the third page. (Ten rows are displayed per page.)

USE AdventureWorks;
SELECT BusinessEntityID, JobTitle, BirthDate
	FROM HumanResources.Employee
	WHERE Gender = 'F'
	ORDER BY JobTitle
	OFFSET 20 ROWS
	FETCH NEXT 10 ROWS ONLY;Code language: SQL (Structured Query Language) (sql)

The OFFSET clause specifies the number of rows to skip before starting to return the rows. This is evaluated after the ORDER BY clause is evaluated and the rows are sorted. The FETCH NEXT clause specifies the number of rows to retrieve. The parameter of this clause can be a constant, an expression, or a result of a query. FETCH NEXT is analogous to FETCH FIRST.

The main purpose of server-side paging is to implement generic page forms, using variables. This can be done using a SQL Server batch. The corresponding example can be found in Chapter “Stored Procedures and User-Defined Functions”.