www.mimer.com/developer

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;

Result:

HOTELCODE	ROOMTYPE	FROM_DATE	TO_DATE	PRICE
SKY	SSGLS	1997-08-08	1997-11-14	750
STG	SSGLS	1997-08-08	1997-11-14	680
LAP	SSGLS	1997-08-08	1997-11-14	680
STG	SSGLS	1997-11-15	1998-03-10	640
LAP	SSGLS	1997-11-15	1998-03-10	640

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;

Result:

HOTELCODE	ROOMTYPE	FROM_DATE	TO_DATE	PRICE
LAP	NSSGLS	1997-11-15	1998-03-10	640
LAP	NSSGLS	1997-08-08	1997-11-14	680
SKY	NSSGLS	1997-08-08	1997-11-14	750
STG	NSSGLS	1997-11-15	1998-03-10	640
STG	NSSGLS	1997-08-08	1997-11-14	680

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;

Result:

ROOMTYPE	AVERAGE_PRICE
NSSGLS	793
SSGLS	793
NSDBLS	910
NSSGLB	910
SDBLS	910
SSGLB	910
NSDBLB	1128
SDBLB	1128

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 dbtechnology@upright.se