Re: SQL Help me

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);

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 Thu Jun 07 2001 - 10:20:26 CEST