7.24.4 Examples

Organization of this subsection

(1) Examples of specifying a sort specification list in an ORDER BY clause
(2) Example of specifying a sort specification list in a WITHIN group specification
(3) Example of specifying a sort specification list in a window order clause

(1) Examples of specifying a sort specification list in an ORDER BY clause

Example 1 (Specifying one column as the sort key)

This example sorts all the data in the customer table (USERSLIST) by customer ID (USERID).

SELECT "USERID","NAME","SEX"
    FROM "USERSLIST"
    ORDER BY "USERID" ASC

The underlined portion indicates the sort specification list.

Example 2 (Specifying multiple columns as sort keys)

This example sorts all the data in the sales history table (SALESLIST) by date of purchase (PUR-DATE). When the date of purchase is the same, this example sorts by customer ID (USERID).

SELECT "USERID","PUR-CODE","PUR-NUM","PUR-DATE"
    FROM "SALESLIST"
    ORDER BY "PUR-DATE" ASC,"USERID" ASC

The underlined portion indicates the sort specification list.

Example 3 (Specifying a value expression as the sort key)

This example extracts eight characters of data starting at the third character from the beginning of the sales history code (HIS-CODE), and then uses the extracted data as the key to sort all the data in the sales history table (SALESLIST).

SELECT * FROM "SALESLIST"
    ORDER BY SUBSTR("HIS-CODE",3,8) ASC

The underlined portion indicates the sort specification list.

Example 4 (Specifying a sort item specification number as the sort key)

Using the data in the sales history table (SALESLIST), this example determines the total quantity purchased (PUR-NUM) for each product code (PUR-CODE), and sorts the retrieval results by total quantity purchased.

SELECT "PUR-CODE",SUM("PUR-NUM")
    FROM "SALESLIST"
    GROUP BY "PUR-CODE"
    ORDER BY 2 ASC

The underlined portion indicates the sort specification list.

Example 5 (Specifying a null-value sort order)

This example sorts all the data in the sales history table (SALESLIST) by date of purchase (PUR-DATE). The rows for which PUR-DATE is the null value come at the top of the sort results.

SELECT "USERID","PUR-CODE","PUR-NUM","PUR-DATE"
    FROM "SALESLIST"
    ORDER BY "PUR-DATE" ASC NULLS FIRST

The underlined portion indicates the sort specification list.

To Page Top

(2) Example of specifying a sort specification list in a WITHIN group specification

Example

Using the data in the salary table (SALARYLIST), this example determines the median value (50^th percentile) of the employee salaries (SALARY).

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY "SALARY") "PERCENTILE_CONT"
    FROM "SALARYLIST"

The underlined portion indicates the sort specification list.

To Page Top

(3) Example of specifying a sort specification list in a window order clause

Example

Using the data in the salary table (SALARYLIST), this example ranks employees by salary (SALARY) within each job class (POSITION).

SELECT "EMPID","POSITION","SALARY",
        RANK() OVER(PARTITION BY "POSITION" ORDER BY "SALARY" DESC) "RANK"
    FROM "SALARYLIST"
    ORDER BY "POSITION","SALARY" DESC,"EMPID"

The underlined portion indicates the sort specification list in the window order clause.

To Page Top