The SQL SELECT statement returns a result set of records from one or more tables.A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used Data Manipulation Language command...
An SQL result set is a set of rows from a database, as well as meta-information about the query such as the column names, and the types and sizes of each column. Depending on the database system, the number of rows in the result set may or may not be known. Usually, this number is not known up...
with the rows being sorted by the values of one or more columns. The sort criteria do not have to be included in the result set. The sort criteria can be expressions, including – but not limited to – column names, user-defined functions
User Defined Function
A User-Defined Function, or UDF, is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment.-BASIC language:...
, arithmetic operations, or CASE expressions. The expressions are evaluated and the results are used for the sorting, i.e. the values stored in the column or the results of the function call.
ORDER BY is the only way to sort the rows in the result set. Without this clause, the relational database system
Database system
A database system is a term that is typically used to encapsulate the constructs of a data model, database Management system and database....
may return the rows in any order. If an ordering is required, the ORDER BY must be provided in the SELECT statement sent by the application. Although some database systems allow the specification of an ORDER BY clause in subselects or view
View (database)
In database theory, a view consists of a stored query accessible as a virtual table in a relational database or a set of documents in a document-oriented database composed of the result set of a query or map and reduce functions...
definitions, the presence there has no effect. A view is a logical relational table, and the relational model mandates that a table is a set of rows, implying no sort order whatsoever. The only exception are constructs like ORDER BY ORDER OF ... (not standardized in SQL:2003
SQL:2003
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
) which allow the propagation of sort criteria through nested subselects.
The SQL standard's core functionality does not explicitly define a default sort order for Null
Null (SQL)
Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL Null serves to fulfill the requirement that all true relational database management systems support...
SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...
extension T611, "Elementary OLAP operations", nulls can be sorted before or after all data values by using the NULLS FIRST or NULLS LAST clauses of the ORDER BY list, respectively. Not all DBMS vendors implement this functionality, however. Vendors who do not implement this functionality may specify different treatments for Null sorting in the DBMS.
Structure ORDER BY ... DESC will order in descending order, otherwise ascending order is used. (The latter may be specified explicitly using ASC.)
Examples
SELECT * FROM Employees
ORDER BY LastName, FirstName
This sorts by the LastName field, then by the FirstName field if LastName matches.
The source of this article is wikipedia, the free encyclopedia. The text of this article is licensed under the GFDL.
