Limit the rows that are retrieved by a query.
The rows returned by a query can be limited with the use of a where clause. The where clause allows you to compare columns retrieved in the select with values, or other columns using operators.
Common operators include: = like > < >= <= "is null" "is not null" "between" and "in" for comparisons against a set.
Example:
SELECT table_name FROM all_tables WHERE table_name = 'FOO' pct_free > 10 AND num_rows BETWEEN 10 AND 20;
In oracle, the built in column rownum can be compared with an integer to limit the size of the returned result set.
Example:
SELECT table_name FROM all_tables WHERE rownum < 10; --returns just 10 rows
Sort the rows that are retrieved by a query.
The rows retrieved by a query can be given an order with the "order by" clause. The order by clause takes a selected column and ascending, or descending as parameters, numerous columns can be specified to apply sub ordering when field are equal.
Example:
SELECT * FROM all_tables ORDER BY owner desc, table_name asc -- orders tables by owner in descending and table name ascending