Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

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
Mimer SQL User's Manual TOC PREV NEXT INDEX