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

It is a 'column alias' so that the results of the sub-select can be referred to by the name 'ranking' elsewhere in the query.

John S. Fetzik
*REMOVE*jfetzik_at_sandc.com

andreyNSPAM_at_bookexchange.net (NetComrade) wrote:

>What's 'as ranking'?
>
>I don't completely understand this, and getting the following error
> (SELECT
> *
>ERROR at line 3:
>ORA-00936: missing expression
>
>
>On Thu, 21 Oct 1999 19:36:13 -0400, "Van Messner"
><vmessner_at_netaxis.com> 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
>
>
>---------------
>Andrey Dmitriev eFax: (978) 383-5892 Daytime: (917) 373-5417
>AOL: NetComrade ICQ: 11340726 remove NSPAM to email
Received on Fri Oct 22 1999 - 22:18:21 CEST