Xref: alice comp.databases.oracle.server:71147 comp.databases.oracle.tools:34347
Path: alice!news-feed.fnsi.net!netnews.com!newsfeed.berkeley.edu!ihug.co.nz!news.tig.com.au!not-for-mail
From: "Dave Waterworth" <pscdaw@ihug.com.au>
Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: SQL PUZZLE: selecting top 50 salaries in a table
Date: Sat, 23 Oct 1999 10:09:18 +1000
Organization: The Internet Group Ltd
Lines: 81
Message-ID: <7uqug9$bki$1@toto.tig.com.au>
References: <380f8e89.268436409@news.earthlink.net> <9sNP3.26581$E_1.1430780@typ11.nn.bcandid.com> <38102EEB.90C8C627@it.lv> <uDAQOPE5NUK+Ws3AhnWy3Z7hLHJ6@4ax.com> <7uq01p$3kc$1@toto.tig.com.au>
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300

Actually thinking about what you want, I think it should be

SELECT * FROM (SELECT SALARY,COUNT(*) FROM ... GROUP BY SALARY ORDER BY
SALARY DESC) WHERE ROWNUM < 51;

The inner query returns a row for every distinct salary in the table, the
outer query picks the higest fifty. My original query would have returned
duplicates (Assuming there are mutliple records with same salary which would
seem reasonable)

Any Help.


Dave Waterworth <pscdaw@ihug.com.au> wrote in message
news:7uq01p$3kc$1@toto.tig.com.au...
> You could use
>
> SELECT * FROM (SELECT SALARY FROM ... ORDER BY SALARY DESC) WHERE ROWNUM <
> 51;
>
> Should work, not sure if its the most efficient way of doing it though,
> depends on the size of the table and whether the SALARY column is indexed.
>
> Dave Waterworth
>
> Jason Salter <jason@seahorseNOSPAM.demon.co.uk> wrote in message
> news:uDAQOPE5NUK+Ws3AhnWy3Z7hLHJ6@4ax.com...
> > On Fri, 22 Oct 1999 09:31:23 GMT, Maris Gabalins <mg@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@bookexchange.net> wrote in message
> > >> news:380f8e89.268436409@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
> > >> >
> >
>
>