Oracle Top 'N' Queries

If we wanted to query all our Customers from an Oracle database, show the Account Code, Name and Balance and order by the Balance in descending order we would use something like this:

select accm1_cus_sup as Code, accmname as Name, accmledg_balance as Balance
from accm_data
where accm1_co_site = '01'
and accm1_rec_type = 1
order by accmledg_balance desc

What if we wanted to retrieve only the Top 10 Customers who owe us the most money ? The best way of doing that is as follow:

select * from
(
select accm1_cus_sup as Code, accmname as Name, accmledg_balance as Balance
from accm_data
where accm1_co_site = '01'
and accm1_rec_type = 1
order by accmledg_balance desc
)
where rownum <= 10

What we have done here is made our initial query into a subquery and used the ROWNUM pseudocolumn to limit the rows returned by the query. This method uses a table scan initially and the rows outside of the rownum count are discarded, giving you your Top 10 Customers by account balance.
Following on but using a slightly different example, what if we wished to display all our Customers in Account Code order, retrieving only ten at a time, maybe within a thin client or web page scenario ? This is how that can be accomplished:

select *
from ( select X.*, ROWNUM rownumber
from
(
select accm1_cus_sup as Code, accmname as Name
from accm_data
where accm1_co_site = '01'
and accm1_rec_type = 1
order by accm1_cus_sup
)
X
where ROWNUM <= 10)
where rownumber >= 0

Obviously when the user indicates they require the next 10 records you would alter the parameters within the final two WHERE statements accordingly. An important point here is that if the column you are using to order the data is not unique, you must append a unique identifier to the ORDER BY statement. This is usually done by adding TABLENAME.ROWID to the end of the ORDER BY statement.

"When you make a mistake, there are only three things you should ever do about it: admit it, learn from it, and don't repeat it." - Paul ‘Bear’ Bryant