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 JOIN items ON products.product_id = items.product_id
    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 Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se