Unless otherwise requested, the database server returns the rows of a table in an order that has no meaning. Often it is useful to look at the rows in a table in a more meaningful sequence. For example, you might like to see products in alphabetical order.
You order the rows in a result set by adding an ORDER BY clause to the end of the SELECT statement. This SELECT statement has the following syntax:
SELECT column-name-1, column-name-2,... FROM table-name ORDER BY order-by-column-name
You must replace column-name-1, column-name-2, and table-name with the names of the columns and table you are querying, and order-by-column-name with a column in the table. As before, you can use the asterisk as a short form for all the columns in the table.
In Interactive SQL, execute the following query:
SELECT ID, Name, Description FROM Products ORDER BY Name; |
ID | Name | Description |
---|---|---|
400 | Baseball Cap | Cotton Cap |
401 | Baseball Cap | Wool cap |
700 | Shorts | Cotton Shorts |
600 | Sweatshirt | Hooded Sweatshirt |
... | ... | ... |
The order of clauses is important The ORDER BY clause must follow the FROM clause and the SELECT clause.
You can specify either ascending or descending order The default order is ascending. You can specify a descending order by adding the keyword DESC to the end of the clause, as in the following query:
SELECT ID, Quantity FROM Products ORDER BY Quantity DESC; |
ID | Quantity |
---|---|
400 | 112 |
700 | 80 |
302 | 75 |
301 | 54 |
600 | 39 |
... | ... |
You can order by several columns The following query sorts first by size (alphabetically), and then by name:
SELECT ID, Name, Size FROM Products ORDER BY Size, Name; |
ID | Name | Size |
---|---|---|
600 | Sweatshirt | Large |
601 | Sweatshirt | Large |
700 | Shorts | Medium |
301 | Tee Shirt | Medium |
... | ... | ... |
The ORDER BY column does not need to be in the select list The following query sorts products by unit price, even though the price is not included in the result set
SELECT ID, Name, Size FROM Products ORDER BY UnitPrice; |
ID | Name | Size |
---|---|---|
500 | Visor | One size fits all |
501 | Visor | One size fits all |
300 | Tee Shirt | Small |
400 | Baseball Cap | One size fits all |
... | ... | ... |
If you do not use an ORDER BY clause, and you execute a query more than once, you may appear to get different results This is because SQL Anywhere may return the same result set in a different order. In the absence of an ORDER BY clause, SQL Anywhere returns rows in whatever order is most efficient. This means the appearance of result sets may vary depending on when you last accessed the row and other factors. The only way to ensure that rows are returned in a particular order is to use ORDER BY.
Using indexes to improve ORDER BY performance
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |