Collection Contents Index The HAVING clause: selecting groups of data The UNION operation: combining queries pdf/chap5.pdf

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.

Top of page  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 

Top of page  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

Top of page  

Collection Contents Index The HAVING clause: selecting groups of data The UNION operation: combining queries pdf/chap5.pdf