Order by: Difference between revisions
mNo edit summary |
Fjerdingen (talk | contribs) Various corrections. |
||
| (30 intermediate revisions by 26 users not shown) | |||
| Line 1: | Line 1: | ||
An '''<code>ORDER BY</code>''' clause in [[SQL]] specifies that a SQL <code>[[Select (SQL)|SELECT]]</code> statement returns a [[result set]] with the rows being sorted by the values of one or more columns. The sort criteria does not have to be included in the result set |
An '''<code>ORDER BY</code>''' clause in [[SQL]] specifies that a SQL <code>[[Select (SQL)|SELECT]]</code> statement returns a [[result set]] with the rows being sorted by the values of one or more columns. The sort criteria does not have to be included in the result set (restrictions apply for <code>SELECT DISTINCT</code>, <code>GROUP BY</code>, <code>UNION [DISTINCT]</code>, <code>EXCEPT [DISTINCT]</code> and <code>INTERSECT [DISTINCT]</code>.) The sort criteria can be expressions, including column names, [[user defined function|user-defined functions]], arithmetic operations, or <code>CASE</code> 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. |
||
<code>ORDER BY</code> is the ''only'' way to sort the rows in the result set. Without this clause, the relational [[database system]] may return the rows in any order. If an ordering is required, the <code>ORDER BY</code> must be provided in the <code>SELECT</code> statement sent by the application. Although some database systems allow the specification of an <code>ORDER BY</code> clause in |
<code>ORDER BY</code> is the ''only'' way to sort the rows in the result set. Without this clause, the relational [[database system]] may return the rows in any order. If an ordering is required, the <code>ORDER BY</code> must be provided in the <code>SELECT</code> statement sent by the application. Although some database systems allow the specification of an <code>ORDER BY</code> clause in subqueries or [[view (database)|view]] definitions, the presence there has no effect on the final result-set order, but makes sense when combined with a result offset clause or a fetch first clause. 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 <code>ORDER BY ORDER OF ...</code> (not standardized in [[SQL:2003]]) which allow the propagation of sort criteria through nested subqueries. |
||
The SQL standard does not explicitly define a default sort order for [[Null (SQL)|Null]]s. |
The SQL standard's core functionality does not explicitly define a default sort order for [[Null (SQL)|Null]]s. With the [[SQL:2003]] extension T611, "Elementary OLAP operations", nulls can be sorted before or after all data values by using the <code>NULLS FIRST</code> or <code>NULLS LAST</code> clauses of the <code>ORDER BY</code> 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.<ref name="sqlitenulls">{{cite web | url=http://www.sqlite.org/nulls.html | title = NULL Handling in SQLite Versus Other Database Engines |access-date=January 25, 2009 }}</ref> |
||
Structure '''<code>ORDER BY ... DESC</code>''' will order in descending order, otherwise ascending order is used. (The latter may be specified explicitly using '''<code>ASC</code>'''.) |
|||
== Examples == |
== Examples == |
||
< |
<syntaxhighlight lang="sql"> |
||
SELECT * FROM Employees |
SELECT * FROM Employees |
||
ORDER BY LastName, FirstName |
ORDER BY LastName, FirstName |
||
</syntaxhighlight> |
|||
</source> |
|||
This sorts by the LastName column, then by the FirstName column if LastName matches. |
|||
==References== |
== References == |
||
{{ |
{{Reflist}} |
||
{{SQL}} |
|||
== External links == |
|||
* [http://beginner-sql-tutorial.com SQL Tutorial] |
|||
{{databases}} |
|||
| ⚫ | |||
| ⚫ | |||
{{DEFAULTSORT:Order By}} |
|||
[[Category:SQL keywords]] |
[[Category:SQL keywords]] |
||
[[Category:Articles with example SQL code]] |
|||
[[ru:ORDER BY]] |
|||
| ⚫ | |||
| ⚫ | |||
Latest revision as of 20:30, 1 June 2023
An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns. The sort criteria does not have to be included in the result set (restrictions apply for SELECT DISTINCT, GROUP BY, UNION [DISTINCT], EXCEPT [DISTINCT] and INTERSECT [DISTINCT].) The sort criteria can be expressions, including column names, user-defined functions, 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 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 subqueries or view definitions, the presence there has no effect on the final result-set order, but makes sense when combined with a result offset clause or a fetch first clause. 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) which allow the propagation of sort criteria through nested subqueries.
The SQL standard's core functionality does not explicitly define a default sort order for Nulls. With the SQL:2003 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.[1]
Structure ORDER BY ... DESC will order in descending order, otherwise ascending order is used. (The latter may be specified explicitly using ASC.)
Examples[edit]
SELECT * FROM Employees
ORDER BY LastName, FirstName
This sorts by the LastName column, then by the FirstName column if LastName matches.
References[edit]
- ^ "NULL Handling in SQLite Versus Other Database Engines". Retrieved January 25, 2009.
 | This database-related article is a stub. You can help Wikipedia by expanding it. |
 | This programming-language-related article is a stub. You can help Wikipedia by expanding it. |