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

From: Ian A. MacGregor <ian_at_tethys.SLAC.Stanford.EDU>
Date: 1 Sep 92 14:56:53 GMT
Message-ID: <5398_at_unixhub.SLAC.Stanford.EDU>

In article <>, (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
|> | +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
                         (415) 926-3528  
Received on Tue Sep 01 1992 - 16:56:53 CEST

Original text of this message