Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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. Without the ORDER BY clause, a change in the database contents or updated Mimer SQL version can otherwise change the order.
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

To order a result table by a set function, 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

Ordering by a Computed Value

To order a result table by a computed value, place the computation in the ORDER BY clause.

Example:
 SELECT product
    FROM products NATURAL JOIN items
    WHERE format_id = 2
    ORDER BY stock * price;
Returns:

 PRODUCT
 The Wild, the Innocent and the E Street Shuffle
 Greatest Hits
 On How Life Is
 Snowed In
 Christmas Portrait
 Falling into You
 LaTouché IV: Traditional Cajun Dancin' Music
 Atlanta Homecoming
 Born in the U.S.A.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX