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

[Quoted] [Quoted] 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:40:18 CEST