Answer to SIMPLE SQL QUESTION.

Reply-To: 72072.2122_at_compuserve.com
Sender: 72072.2122_at_compuserve.com

If you use a SQL statement like:

SELECT LAST_NAME
FROM EMP
WHERE ROWNUM < 6
AND LAST_NAME > ' '
/

and the LAST_NAME is indexed, it should return the first 5 rows ordered by the LAST_NAME.

This is because Oracle is forced to use an index when the indexed column is specified in the WHERE clause. This forces the rows to come back in order of the index. An ORDER BY is not required.

The SQL Language Reference Manual (778-V6.0 0290) on page 3-26 states:

"ROWNUM is assigned as the row is retrieved and not affected by any order imposed by the ORDER BY clause (that is, the ROWNUM is assigned before the rows are ordered)."

So this SQL statement"
SELECT LAST_NAME
FROM EMP
WHERE ROWNUM < 6
ORDER BY LAST_NAME
/

will return a random 4 row sampling of data from the EMP table.

It is important to know your data and know your database specification.

                                            chicago ,--------+  detroit
Tom Harleman                                        |        |
INOUG Committee Member                              |Indiana |
Paradigm Consulting, Inc.                 st.       | Oracle |
11080 Willowmere Drive                    louis     |  Users |
Indianapolis, IN  46280                             |   Group|
USA                                                 |        |
                                                    |      _,+cincinnati
INTERNET:   72072.2122_at_compuserve.com               /   _,'
Compuserve: 72072,2122                             /_,-'louisville
                                                   `