Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

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
Mimer SQL Documentation TOC PREV NEXT INDEX