Limiting Selected Rows
2
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder2Ć2
Limiting Selected Rows 2Ć3
Objectives
While retrieving data from the database, you may need to restrict the rows of
data that are displayed or specify the order in which the rows are displayed.
This lesson explains the commands you will use to perform these actions.
At the end of this lesson you should be able to
D
Sort row output using the ORDER BY clause.
D
Enter search criteria using the WHERE clause.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder2Ć4
If the ORDER BY clause is not used, the sort order is undefined, and the Oracle7
Server may not fetch rows in the same order for the same query twice. Use the
ORDER BY clause to display the rows in a specific order.
Limiting Selected Rows 2Ć5
Ordering Rows with the ORDER BY Clause
The order of rows returned in a query result is undefined. The ORDER BY clause
may be used to sort the rows. If used, you must place the ORDER BY clause last.
You can specify an expression or use position to sort.
Syntax
SELECT expr
FROM table
[ORDER BY {column,expr} [ASC|DESC]];
where: ORDER BY specifies the order in which the retrieved rows
are displayed.
ASC orders the rows in ascending order. This is the
default order.
DESC orders the rows in descending order.

D
In Oracle7, null values are displayed last for ascending sequences and first for
descending sequences.
Reversing the Default Order
To reverse the order in which rows are displayed, the command word DESC is
specified after the column name in the ORDER BY clause.
Example
Query the employee table for employee last name, department number, and the hire
date for all employees. Sort the results by the most recently hired employee.
SQL> SELECT last_name, dept_id, start_date
2 FROM s_emp
3 ORDER BY start_date DESC;
LAST_NAME DEPT_ID START_DAT
------------ ------- ---------
...
Urguhart 41 18-JAN-91
Chang 44 30-NOV-90
Patel 34 17-OCT-90
Menchu 42 14-MAY-90
...
25 rows selected.
Ordering with Column Aliases
You can use a column alias in the ORDER BY clause. This feature was made
available in Oracle7 release 7.0.16.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder2Ć8
Limiting Selected Rows 2Ć9
Ordering Rows with the ORDER BY Clause
continued
Ordering by Position
Another method for sorting query results is to sort by position. This is especially

You can restrict the rows returned from the query by using the WHERE clause. A
WHERE clause contains a condition that must be met, and directly follows the
FROM clause.
Syntax
SELECT expr
FROM table
[WHERE condition(s)]
[ORDER BY expr];
where: WHERE restricts the query to rows that meet a condition.
condition is composed of column names, expressions,
constants, and comparison operators.
Comparison Operators
Comparison operators are divided into two categories: logical and SQL. They are
used in the WHERE clause to compare one expression to another using the following
format.
Syntax
...WHERE expr operator value
Example WHERE conditions
...WHERE dept_id = 42
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder2Ć12
Limiting Selected Rows 2Ć13
Comparison Operators
Character Strings and Dates
Character strings and dates in the WHERE clause must be enclosed in single
quotation marks (‘ ’). Number constants, however, must not.
Example
Write a query to show the first and last names, and title for the employee named
“Magee.”
SQL> SELECT first_name, last_name, title
2 FROM s_emp

IS NULL Is a null value
Logical Operators
Operator
Meaning
AND If both component conditions return TRUE, then the
result is TRUE.
OR If either component condition returns TRUE, then the
result is TRUE.
NOT Returns the opposite condition.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder2Ć16