Database Reference
In-Depth Information
6.3
Sorting Methods
Other than sorting by single or multiple column names, the ORDER BY
clause can be used to sort in two other ways:
Sorting by position sorts by the position of a column or expression
within the SELECT statement columns list.
Sorting by expression allows the ORDER BY clause to contain an
expression such as a calculation.
6.3.1
Sorting by Position
The following query sorts in order of the second column, the third column,
and the first column. What's that in English? PLAYING_TIME by TITLE
by RECORDING_DATE or RECORDING_DATE sorted within
TITLE, sorted within PLAYING_TIME.
Note:
Optional modifiers such as NULLS FIRST and DESC apply indi-
vidually to each sorted column in the ORDER BY clause.
Figure 6.8 shows the result.
SELECT RECORDING_DATE, PLAYING_TIME, TITLE
FROM SONG
WHERE TITLE LIKE '%a%'
AND TITLE LIKE '%e%'
AND TITLE LIKE '%i%'
ORDER BY 2 NULLS FIRST, 3 DESC, 1;
There are several points to take note of in Figure 6.8, including the three
annotations:
The PLAYING_TIME column (position 2) has NULLS FIRST and
thus null values are listed at the top.
The TITLE column (position 3) is sorted within the
PLAYING_TIME column (position 2) in descending (DESC) order.
