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

what if you create a sorted view/snapshot/table of the salrary table, then do a runum < 50 on that?

Jason Salter wrote:

> On Fri, 22 Oct 1999 09:31:23 GMT, Maris Gabalins <mg_at_it.lv> wrote:
>
> >select salary
> >from ....
> >where rownum < 51
> >order by salary desc
> >
>
> That would'nt work. The rownum are applied before the sort takes
> place. You'd only get the first 50 rows in the table (which would then
> be sorted), not the first fifty sorted rows.
>
> >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
> >> >
Received on Fri Oct 22 1999 - 11:31:11 CDT