ASA Getting Started
  Selecting Data from Database Tables

Ordering query results

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 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

where column-name-1, column-name-2, and table-name should be replaced with the names of the desired columns and table you are querying, and where order-by-column-name is a column in the table. As before, you can use the asterisk as a short form for all the columns in the table.

List the products in alphabetical order

• In Interactive SQL, type the following in the SQL Statements pane:

  SELECT id, name, description
  FROM product
  ORDER BY name

  id	name	description
  400	Baseball Cap	Cotton Cap
  401	Baseball Cap	Wool cap
  700	Shorts	Cotton Shorts
  600	Sweatshirt	Hooded Sweatshirt
  ...	...	...

Notes 

• 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 product
  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 product
  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 product
  ORDER BY unit_price

  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 Adaptive Server Anywhere may return the same result set in a different order. In the absence of an ORDER BY clause, Adaptive Server 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