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.
Retrieve the hotel code, room type, from date and price for SMOKING single rooms with showers with a cost of under 800 and order by the price in descending order:SELECT * FROM ROOM_PRICES WHERE PRICE < 800 AND ROOMTYPE = 'SSGLS' ORDER BY PRICE DESC;
Ordering by More than One Column
More than one column may be specified in the ORDER BY clause.
Example:SELECT * FROM ROOM_PRICES WHERE PRICE < 800 AND ROOMTYPE = 'NSSGLS' ORDER BY HOTELCODE, PRICE;
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 ROOMTYPE, AVG(PRICE) AS AVERAGE_PRICE FROM ROOM_PRICES GROUP BY ROOMTYPE ORDER BY AVERAGE_PRICE;
The following formulation is incorrect, since there is no PRICE column in the result table by which to perform the ordering:SELECT ROOMTYPE, AVG(PRICE) FROM ROOM_PRICES GROUP BY ROOMTYPE ORDER BY PRICE;
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40