Anticlue

An important distinction to make is that you do not need to use inline views if you do not have an ORDER BY clause in your query. If not, you can simply write:

select account_no, discharge_date,
from encounter
where rownum <= 10

Further, even if you did have an ORDER BY clause and hence needed to use an inline view, you don't need to reference rownum in the parent select query:

select account_no, discharge_date
from (select * from encounter order by discharge_date)
where rownum <=10;

This will return the same rows. And lastly, it is usually vastly more efficient to only select the columns you'll use in the parent SELECT clause in your inline view:

select account_no, discharge_date
from (select account_no, discharge_date from encounter order by discharge_date)
where rownum <=10;

I make these points because the ability to use this sort of functionality at the database level rather than at the ColdFusion level can significantly improve the performance of your applications.

Thanks Elyse for the tip.

Regarding Dave Carabetta's comments from December 9, 2004, these comments are largely useless.

#1: >you do not need to use inline views if you do not have an ORDER BY clause in your query
Dave may be right, but this would seem to be useless. "Top n" queries only makes sense if you use order by. Otherwise you are getting the "top n" records of an undefined record order, which seldom would be useful.

#2: > you don't need to reference rownum in the parent select query
Dave then goes on to reference the rownum in the parent query. Gotcha.

#3: Dave posted his comments on my 15th wedding anniversary.