http://developer.mimer.com

Ordering the Result Table

---

Strictly, the order of rows in a result table is undefined unless an ORDER BY clause is included in the SELECT statement.

Ascending or descending order may be specified; ascending order is the default.

Note: A SELECT statement without an ORDER BY clause may appear to give an ordered result in Mimer SQL, but you should include an ORDER BY clause if the ordering is important. A change in the database contents may otherwise change the order, particularly for a complex query where the order of execution is determined by the SQL optimizer.

Example

 SELECT *
    FROM formats
    ORDER BY format DESC;

Returns:

FORMAT_ID	FORMAT	CATEGORY_ID	DISPLAY_ORDER
4	Vinyl	1	40
10	Video	3	20
8	Paperback	2	20
7	Hardcover	2	10
9	DVD Video	3	10
3	DVD Audio	1	10
2	Cassette	1	30
1	Audio CD	1	20
6	Audio CD	2	30
5	Audio Cassette	2	40

Ordering by More than One Column

More than one column may be specified in the ORDER BY clause.

Example:

 SELECT *
    FROM formats
    ORDER BY category_id, display_order;

Returns:

FORMAT_ID	FORMAT	CATEGORY_ID	DISPLAY_ORDER
3	DVD Audio	1	10
1	Audio CD	1	20
2	Cassette	1	30
4	Vinyl	1	40
7	Hardcover	2	10
8	Paperback	2	20
6	Audio CD	2	30
5	Audio Cassette	2	40
9	DVD Video	3	10
10	Video	3	20

Ordering by Set Function or Computed Value

To order a result table by a set function or computed value, the column in the result table is given a label and the label is used in the ORDER BY clause.

Example:

 SELECT category_id, MAX(display_order) AS maximum_value
    FROM formats
    GROUP BY category_id
    ORDER BY maximum_value;

Returns:

CATEGORY_ID	MAXIMUM_VALUE
3	20
1	40
2	40

The following query is incorrect, since there is no DISPLAY_ORDER column in the result table by which to perform the ordering:

 SELECT category_id, MAX(display_order)
    FROM formats
    GROUP BY category_id
    ORDER BY display_order;

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se