Database Reference
In-Depth Information
Figure 5.6
The Bracketed
Expression Is
Evaluated First.
A Top-N query uses an Inline view and the ROWNUM pseudocolumn
to retrieve data from a large table in a specified order. An Inline view is a
type of subquery (see Chapter 12).
The following query looks at the recording date and title of songs in the
SONG table. The query uses an Inline view in place of a table in the
FROM clause.
SELECT RECORDING_DATE, TITLE
FROM (SELECT RECORDING_DATE, TITLE
FROM SONG
WHERE RECORDING_DATE <= '24-JAN-01'
ORDER BY RECORDING_DATE);
Let's imagine that this table actually contains millions of rows and you
only want to see the first 10 rows that were recorded the earliest. The rows
will be sorted by date within the Inline view. By using the ROWNUM
pseudocolumn in the main query, you can quickly retrieve only the first few
rows of the table. Change the query to add the ROWNUM pseudocolumn
in the WHERE clause. The complete query should look as follows with the
change highlighted:
SELECT RECORDING_DATE, TITLE
FROM (SELECT RECORDING_DATE, TITLE
FROM SONG
WHERE RECORDING_DATE <= '24-JAN-01'
