User's Guide
   PART 1. Working with Databases
     CHAPTER 5. Summarizing, Grouping, and Sorting Query Results       

The ORDER BY clause: sorting query results

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.

A simple example 

The following query returns results ordered by name:

SELECT id, name
FROM product
ORDER BY name 

id	name
400	Baseball Cap
401	Baseball Cap
700	Shorts
600	Sweatshirt
601	Sweatshirt
300	Tee Shirt
301	Tee Shirt
302	Tee Shirt
500	Visor
501	Visor

Sorting by more than one column 

If you name more than one column in the ORDER BY clause, the sorts are nested.

The following statement sorts the shirts in the product table first by name in ascending order, then by quantity (descending) within each name:

SELECT id, name, quantity
FROM product
WHERE name like '%shirt%'
ORDER BY name , quantity DESC

id	name	quantity
600	Sweatshirt	Baseball Cap
601	Sweatshirt	Baseball Cap
302	Tee Shirt	Shorts
301	Tee Shirt	Sweatshirt
300	Tee Shirt	Sweatshirt

Using the column position 

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 product
WHERE name like '%shirt%'
ORDER BY 2 , 3 DESC
SELECT id, name, quantity
FROM product
WHERE name like '%shirt%'
ORDER BY 2 , quantity DESC

Most versions of SQL require that ORDER BY items appear in the select list, but Adaptive Server Anywhere has no such restriction. You could order the results of the preceding query by size, although that column does not appear in the select list.

ORDER BY and NULL 

With ORDER BY, NULL comes before all other values, whether the sort order is ascending or descending.

ORDER BY and case sensitivity 

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.

  Retrieving the first few rows of a query

You can limit the results of a query to the first few rows returned using the FIRST or TOP keywords. While you can use these with any query, they are most useful with queries that use the ORDER BY clause.

Examples 

• The following query returns information about the first employee sorted by last name:

  SELECT FIRST *
  FROM employee
  ORDER BY emp_lname

• The following query returns the first five employees sorted by last name comes earliest in the alphabet:

  SELECT TOP 5 *
  FROM employee
  ORDER BY emp_lname

  ORDER BY and GROUP BY

You can use an ORDER BY clause to order the results of a GROUP BY in a particular way.

Example 

  To find the average price of each type of book and order the results by average price:

1. Enter the following statement:

   SELECT name, AVG(unit_price) 
   FROM product
   GROUP BY name
   ORDER BY AVG(unit_price) 

   name	AVG(unit_price)
   Visor	7.000
   Baseball Cap	9.500
   Tee Shirt	12.333
   Shorts	15.000
   Sweatshirt	24.000