The ORDER BY clause allows sorting of query results by one or more columns. Each sort can be ascending (ASC) or descending (DESC). If neither is specified, ASC is assumed.
The following query returns results ordered by name:
SELECT ID, Name FROM Products ORDER BY Name; |
ID | Name |
---|---|
400 | Baseball Cap |
401 | Baseball Cap |
700 | Shorts |
600 | Sweatshirt |
... | ... |
If you name more than one column in the ORDER BY clause, the sorts are nested.
The following statement sorts the shirts in the Products table first by name in ascending order, then by Quantity (descending) within each name:
SELECT ID, Name, Quantity FROM Products WHERE Name like '%shirt%' ORDER BY Name, Quantity DESC; |
ID | Name | Quantity |
---|---|---|
600 | Sweatshirt | 39 |
601 | Sweatshirt | 32 |
302 | Tee Shirt | 75 |
301 | Tee Shirt | 54 |
... | ... | ... |
You can use the position number of a column in a select list instead of the column name. Column names and select list numbers can be mixed. Both of the following statements produce the same results as the preceding one.
SELECT ID, Name, Quantity FROM Products WHERE Name like '%shirt%' ORDER BY 2, 3 DESC; SELECT ID, Name, Quantity FROM Products WHERE Name like '%shirt%' ORDER BY 2, Quantity DESC |
Most versions of SQL require that ORDER BY items appear in the select list, but SQL Anywhere has no such restriction. The following query orders the results by Quantity, although that column does not appear in the select list:
SELECT ID, Name FROM Products WHERE Name like '%shirt%' ORDER BY 2, Quantity DESC; |
With ORDER BY, NULL sorts before all other values in ascending sort order.
The effects of an ORDER BY clause on mixed-case data depend on the database collation and case sensitivity specified when the database is created.
Explicitly limiting the number of rows returned by a query
ORDER BY and GROUP BY
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |