Re: rownum

apologies for the cross posting.

> [ The answers you will get using the 'select * from table where rownum <=
 10
> order by field_name' construct will not give you what you expect...it
 will give
> you the first 10 records returned by the select statement and THEN sort
them...)

You're right, the sorting is done after the resultset is generated, not before, which is what I need. After realizing this, I checked out the Java documentation (we're using Java for this project), and quickly found a method which limits resultsets to whatever number you desire, before actually making the call to the select statement. This combination works, the data is sorted as needed, and I only get the top ten of that resultset.

The examples in the responses to my query were too simple to illustrate my point. Here's what I'm actually doing:

The following query is my attempt to take from the employee table all information for employees who belong to a particluar department, ordered by their hire date. The resultset could contain thousands of records, so I request only the top 10 rows **from the resultset**, meaning (in english) "get all information for employees who work in this department, sort it by their hire date in descending order, and give me the the first 10 records from that". Instead, the query (in elnglish) "gives me all information for employees who work in this department and whose rownum is less than or equal to 10, and then sorts the en by hire date". This means that **the first ten records created** will be returned, and then those ten are ordered by hire date.

SELECT * FROM employee WHERE employeeid
IN (SELECT employeeid FROM employee_departments WHERE deptid= '4') AND rownnum <= 5
ORDER BY hiredate DESC

> Also, the rownum is not an internal index but a pseudo-variable that
 counts the
> rows returned as they come in...Not to be confused with ROWID...

That doen't jive with the resultsets I got from the select statement, but maybe I'm still missing something. I'm new to this after all.

Thanks for the help everyone. I'm finally onto other problems. C Received on Tue Feb 06 2001 - 22:16:55 CET