Re: SQL Help me

Bob,

Your method is correct, however, if the original poster wanted a rank from 1 to whatever as well, he will have to do it in a manner similar to what I posted. If no rank is needed, your way is fine.

I think the order by in an inline view still causes problems, i tried it on a query one day and it complained. I'm using 8.1.6.

M

"Robert Yeh" <robert.yeh_at_qwest.com> wrote in message news:<9fntmu$g9n$1_at_bob.news.rcn.net>...
> 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:48 CEST