OOPS the "AND salb.determinant = salb.determinant" in the query should
read "AND sala.determinant = salb.determinant".
James
In article <7uq2cm$3jf$1_at_nnrp1.deja.com>,
James Lorenzen <james_lorenzen_at_allianzlife.com> wrote:
> 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.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Fri Oct 22 1999 - 11:17:18 CDT
