Re: SQL*plus -- limiting maximum number of returned rows?

In article <BtuK40.8By_at_ef2007.efhd.ford.com>, wwm_at_ef2007.efhd.ford.com (William Meahan) writes:
|> In SQL*Plus (not embedded SQL!) is there any way of limiting the number
|> of rows returned by a query?
|>
|> I am working on a report that is supposed to find the "top 5" items in
|> a catagory. The query returns 0 or more rows based on the selection
|> criteria, sorted in the appropriate order. Some queries return a dozen
|> or more rows, but I'm only interested in the first 5.
|>
|> If I were using embedded SQL in a C program (or a perl script using
|> oraperl) the solution would be trivial. For internal reasons, however,
|> I'm stuck with using SQL*Plus.
|>
|> Any ideas?
|>
|>
|> --
|> Bill Meahan
|> --
|> Bill Meahan |EFHD Information Systems Staff
|> Technical Consultant |Ford Motor Company
|> wwm_at_ef2007.efhd.ford.com | +1 313 487 6122
|> ...!fmsrl7!ef2007!wwm |I'm not paid to speak for Ford!

The top 5 articles in a category may return more than 5 rows. The following query selects the people with the 5 highest salaried people from the Oracle EMP table.

     select * from emp  a where 5 >
     (select count(sal) from emp b where
     b.sal > a.sal)
     order by sal desc

If there are ties for the fifth highest paid person all rows containing the "tie" value will be retrieved.

                         Ian MacGregor
                         Stanford Linear Accelerator Center
                         IAN_at_SLAC.STANFORD.EDU
                         (415) 926-3528