Re: SQL Help me

Way too complicated.

Since the the rownum is given before the sort, do this:

select column1
from (select column1 from table1 order by from column1) where rownum < 20;

Note: this only work in 8.1 or above. If you are using an older version you can't put the order by inside the in-line table(I probably use the wrong term here).

Do this for an older version.
select column1
from (select column1 from table1 a, dual b where a.column1=b.dummy(+)) where rownum < 20;

This works because oracle sorts column1 when doing the joint. Again you can't use this if you want to sort column1 by descending order.

Bob

MarkyG wrote in message ...
>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 Sat Jul 21 2001 - 23:10:34 CEST