Re: SQL Help me

Actually it'll select the first 20 ordered by Customer ID.

I presume that that was what he meant by "rank"

On 7 Jun 2001 01:20:26 -0700, markg_at_mymail.tm (MarkyG) wrote:

>This is the age old problem and the age old incorrect solution.
>
>> SELECT *
>> FROM CUSTOMER
>> WHERE ROWNUM < 21
>> ORDER BY CUSTOMER_ID
>
>This will select the first 20 records from the CUSTOMER table (the
>first 20 in the way Oracle stores its data and not in any real world
>order). The order by will just order that 'randon' first 20 records.
>
>To really get what you want consider the following example (in Tom
>Kyte style ;-) )
>
>create table customer
>(customer_id number, salary number);
>insert into customer values(100, 1345);
>insert into customer values(101, 145);
>insert into customer values(102, 62347);
>insert into customer values(103, 72746);
>insert into customer values(104, 124435);
>insert into customer values(105, 12632);
>insert into customer values(106, 12);
>
>The following select the highest 5 earners and puts a rank against
>them.
>
>SELECT
> COUNT(DISTINCT b.customer_id) rank,
> a.customer_id,
> a.sal
>FROM
> (SELECT customer_id, SUM(salary) sal
> FROM customer
> GROUP BY customer_id) a,
> (SELECT customer_id, SUM(salary) sal
> FROM customer
> GROUP BY customer_id) b
>WHERE
> a.sal <= b.sal
>HAVING COUNT(DISTINCT b.customer_id) <= 5
>GROUP BY
> a.customer_id,
> a.sal
>ORDER BY
> COUNT(DISTINCT b.customer_id)
>/
>
>SQL> /
>
> RANK CUSTOMER_ID SAL
>---------- ----------- ----------
> 1 104 124435
> 2 103 72746
> 3 102 62347
> 4 105 12632
> 5 100 1345
>
>Hope that helps.
>
>Mark
>
>
>
>JDoe <x_at_x.com> wrote in message news:<nbftht8uf2hhrbvjpeo44mp8lq66tdfm1l_at_4ax.com>...
>> SELECT *
>> FROM CUSTOMER
>> WHERE ROWNUM < 21
>> ORDER BY CUSTOMER_ID
>>
>> That should do it.
>>
>> On 6 Jun 2001 01:58:33 -0700, korn_at_usa.net (Kittikorn Tongnimitsawat)
>> wrote:
>>
>> >Dear all,
>> >
>> >I am using Oracle Financial Service Apprication, OFSA. I need the SQL
>> >to select just first 20 ranks of the result. For example, SELECT *
>> >FROM customer ORDER BY customer_id; this statement shows me a number
>> >of reccord. I just want the first 20 record showed to me. I don't know
>> >how to do.
>> >
>> >Best regards,
>> >Korn
Received on Mon Jun 11 2001 - 12:52:17 CEST