Re: SQL PUZZLE: selecting top 50 salaries in a table

Create an index (desc) on salary and force its use in the query? The rows will be returned in descending order and rownum will then do the trick. Of course, you might not *want* an index on salary.

Of course, this is a doddle in RPT (or PL*SQL, as noted :> )

Cheers

Glenn Baron

James Lorenzen <james_lorenzen_at_allianzlife.com> wrote in article <7uq2cm$3jf$1_at_nnrp1.deja.com>...
> NOPE. The rownum pseudo column is populated as the rows are returned
> from the the query and this is BEFORE the order by is applied. This
> very simple solution is proposed almost very time a question is raised
> on returning the (TOP/BOTTOM) N Rows of a table.
>
> Van Messner’s solution does not work on my 7.3.4 installation. The
> SELECT as a column does not cut it in Oracle 7, I don’t know about
> Oracle 8.
>
> If you do not want to do this in PL*SQL (the “easiest” solution). Then
> you may try this code. Please note, it is not very fast. If you only
> need the salary and rank, then the inline view is all that is needed.
>
> SELECT rank.ranking, sal1.salary, sal1.{other info}
> FROM MySalaryTable sal1,
> (SELECT sala.salary, COUNT(distinct salb.salary) Ranking
> FROM MySalaryTable sala, MySalaryTable salb
> WHERE sala.salary <= salb.salary
> AND salb.determinant = salb.determinant
> GROUP BY sala.salary) rank
> WHERE sal1.salary = rank.salary
> AND ranking <= &n
> ORDER BY Ranking
>
> HTH
> James
>
> In article <38102EEB.90C8C627_at_it.lv>,
> Maris Gabalins <mg_at_it.lv> wrote:
> > select salary
> > from ....
> > where rownum < 51
> > order by salary desc
> >
> > Van Messner wrote:
> >
> > > I don't have Oracle in front of me but please try below where n is
> how many
> > > top records to select, MySalaryTable has the salary information and
> > > determinant is any other condition (the 50 top salaries for 1997 for
> > > example). This query should allow for ties, which in the case of
> salaries,
> > > are likely to occur.
> > >
> > > SELECT
> > > sal1.salary,
> > > (SELECT
> > > COUNT(DISTINCT salary)
> > > FROM
> > > MySalaryTable sal2
> > > WHERE
> > > sal2.salary >= sal1.salary
> > > and sal2.determinant = sal1.determinant) as ranking
> > > FROM
> > > MySalaryTable sal1
> > > Where
> > > ranking <= &n
> > > ;
> > >
> > > Van
> > >
> > > NetComrade <andreyNSPAM_at_bookexchange.net> wrote in message
> > > news:380f8e89.268436409_at_news.earthlink.net...
> > > > Well.. we know how to select top salary, which is select
> max(salary),
> > > > but how would I select top 50 records?
> > > > ---------------
> > > > Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
> > > > AOL: NetComrade ICQ: 11340726 remove NSPAM to email
> > > >
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Fri Oct 22 1999 - 11:53:10 CDT