174 lines
9.2 KiB
HTML
174 lines
9.2 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="ORDER BY clause" />
|
||
|
|
<meta name="abstract" content="The ORDER BY clause specifies the particular order in which you want selected rows returned. The order is sorted by ascending or descending collating sequence of a column's or expression's value." />
|
||
|
|
<meta name="description" content="The ORDER BY clause specifies the particular order in which you want selected rows returned. The order is sorted by ascending or descending collating sequence of a column's or expression's value." />
|
||
|
|
<meta name="DC.subject" content="SELECT statement, ORDER BY, example, clause, ORDER BY, SELECT statement, using NULL values with, NULL value, used with ORDER BY clause" />
|
||
|
|
<meta name="keywords" content="SELECT statement, ORDER BY, example, clause, ORDER BY, SELECT statement, using NULL values with, NULL value, used with ORDER BY clause" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafytexas.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafygroup.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rbafyussisql.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="rbafyorderby" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>ORDER BY clause</title>
|
||
|
|
</head>
|
||
|
|
<body id="rbafyorderby"><a name="rbafyorderby"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">ORDER BY clause</h1>
|
||
|
|
<div><p>The ORDER BY clause specifies the particular order in which you
|
||
|
|
want selected rows returned. The order is sorted by ascending or descending
|
||
|
|
collating sequence of a column's or expression's value.</p>
|
||
|
|
<div class="section"><p>For example, to retrieve the names and department numbers of female
|
||
|
|
employees listed in the alphanumeric order of their department numbers, you
|
||
|
|
can use this select-statement:</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><pre><strong>SELECT</strong> LASTNAME,WORKDEPT
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>WHERE</strong> SEX='F'
|
||
|
|
<strong>ORDER BY</strong> WORKDEPT</pre>
|
||
|
|
<p> Results in: </p>
|
||
|
|
|
||
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="hsides" border="1" rules="all"><thead align="left"><tr><th valign="top" id="d0e76">LASTNAME</th>
|
||
|
|
<th valign="top" id="d0e78">WORKDEPT</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody><tr><td valign="top" headers="d0e76 ">HAAS</td>
|
||
|
|
<td valign="top" headers="d0e78 ">A00</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">HEMMINGER</td>
|
||
|
|
<td valign="top" headers="d0e78 ">A00</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">KWAN</td>
|
||
|
|
<td valign="top" headers="d0e78 ">C01</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">QUINTANA</td>
|
||
|
|
<td valign="top" headers="d0e78 ">C01</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">NICHOLLS</td>
|
||
|
|
<td valign="top" headers="d0e78 ">C01</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">NATZ</td>
|
||
|
|
<td valign="top" headers="d0e78 ">C01</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">PIANKA</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">SCOUTTEN</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">LUTZ</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">JOHN</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">PULASKI</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D21</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">JOHNSON</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D21</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">PEREZ</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D21</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">HENDERSON</td>
|
||
|
|
<td valign="top" headers="d0e78 ">E11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">SCHNEIDER</td>
|
||
|
|
<td valign="top" headers="d0e78 ">E11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">SETRIGHT</td>
|
||
|
|
<td valign="top" headers="d0e78 ">D11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">SCHWARTZ</td>
|
||
|
|
<td valign="top" headers="d0e78 ">E11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">SPRINGER</td>
|
||
|
|
<td valign="top" headers="d0e78 ">E11</td>
|
||
|
|
</tr>
|
||
|
|
<tr><td valign="top" headers="d0e76 ">WONG</td>
|
||
|
|
<td valign="top" headers="d0e78 ">E21</td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"> <div class="note"><span class="notetitle">Note:</span> Null values are ordered as the highest value.</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The column specified in the ORDER BY clause does not need to be
|
||
|
|
included in the SELECT clause. For example, the following statement will return
|
||
|
|
all female employees ordered with the largest salary first: </p>
|
||
|
|
<pre><strong>SELECT</strong> LASTNAME,FIRSTNME
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>WHERE</strong> SEX='F'
|
||
|
|
<strong>ORDER BY</strong> SALARY DESC</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>If an AS clause is specified to name a result column in the select-list,
|
||
|
|
this name can be specified in the ORDER BY clause. The name specified in
|
||
|
|
the AS clause must be unique in the select-list. For example, to retrieve
|
||
|
|
the full name of employees listed in alphabetic order, you can use this select-statement:
|
||
|
|
</p>
|
||
|
|
<pre> <strong>SELECT</strong> LASTNAME <strong>CONCAT</strong> FIRSTNME <strong>AS</strong> FULLNAME
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>ORDER BY</strong> FULLNAME</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>This select-statement can optionally be written as: </p>
|
||
|
|
<pre> <strong>SELECT</strong> LASTNAME <strong>CONCAT</strong> FIRSTNME
|
||
|
|
<strong>FROM</strong> CORPDATA.EMPLOYEE
|
||
|
|
<strong>ORDER BY</strong> LASTNAME <strong>CONCAT</strong> FIRSTNME</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Instead of naming the columns to order the results, you can use
|
||
|
|
a number. For example, ORDER BY 3 specifies that you want the results ordered
|
||
|
|
by the <em>third</em> column of the results table, as specified by the select-list.
|
||
|
|
Use a number to order the rows of the results table when the sequencing value
|
||
|
|
is not a named column.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>You can also specify whether you want SQL to collate the rows
|
||
|
|
in ascending (ASC) or descending (DESC) sequence. An ascending collating sequence
|
||
|
|
is the default. In the previous select-statement, SQL first returns the row
|
||
|
|
with the lowest <em>FULLNAME</em> expression (alphabetically and numerically),
|
||
|
|
followed by rows with higher values. To order the rows in descending collating
|
||
|
|
sequence based on this name, specify: </p>
|
||
|
|
<pre>… <strong>ORDER BY</strong> FULLNAME <strong>DESC</strong></pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>You can specify a secondary ordering sequence (or
|
||
|
|
several levels of ordering sequences) as well as a primary one. In the previous
|
||
|
|
example, you might want the rows ordered first by department number, and within
|
||
|
|
each department, ordered by employee name. To do this, specify: </p>
|
||
|
|
<pre>… <strong>ORDER BY</strong> WORKDEPT, FULLNAME</pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>If character columns, or UCS-2 or UTF-16 graphic columns are used
|
||
|
|
in the ORDER BY clause, ordering for these columns is based on the sort sequence
|
||
|
|
in effect when the query is run. </p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rbafytexas.htm" title="Learn a variety of ways of tailoring your query to gather data using the SELECT statement. One way to do this is to use the SELECT statement in a program to retrieve a specific row (for example, the row for an employee). Furthermore, you can use clauses to gather data in a specific way.">Retrieve data using the SELECT statement</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="rbafyussisql.htm" title="A sort sequence defines how characters in a character set relate to each other when they are compared or ordered. Normalization allows you to compare strings that contain combining characters.">Sort sequences and normalization in SQL</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relref"><strong>Related reference</strong><br />
|
||
|
|
<div><a href="rbafygroup.htm" title="The GROUP BY clause allows you to find the characteristics of groups of rows rather than individual rows.">GROUP BY clause</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|