Re: SQL question in sqlplus!

ROWNUM corresponds to the order that rows were inserted into the table: ROWNUM = 1 for the first row inserted, 2 for the second, etc. It ain't elegant, but one way to get only the first three result rows is to break the query into parts:

create table temp_results as
select item, sum(quantity) "quantity" from table group by item;

create table temp_results2 as
select item, quantity from temp_results order by quantity;

select * from temp_results2 where rownum <= 3;

drop table temp_results;
drop table temp_results2;

<ccyr6798_at_my-deja.com> wrote in message news:8386ue$72$1_at_nnrp1.deja.com...
> how about...
> select item, sum(quantity)
> from table
> group by item
> order by item;
>
> not sure how to get the rownum<3 (unless PL/SQL and cursor) - I don't
> quite get the rownum concept but it looks like rownum is fixed to
> rowid, not the results of the query.
>
> anybody want to comment on my perception of rownum???
>
>
>
> In article <833lje$703$1_at_m5.att.net.hk>,
> "Zenith" <jackyleo_at_attmysite.com> wrote:
> > may be my question is too confusing,
> > but actually this means can't solve my problems.
> > i have a table,
> > item quantity
> > P001 2
> > P023 4
> > P001 5....
> > ps. the item is not the key, it can't code can appear many times in
> this
> > column.
> > i want to group the item frist, then use sum() to find out the total
> quanity
> > of each item. and limit to the first three rows..
> > i have tried this:
> > select item, "sum(quantity" from
> > ( select item, sum(quantity)
> > from tbl_name
> > group by item order by sum(quantity)
> > ) where rownum <= 3;
> > how ever, i 've tried different silimar methods,
> > it seems that, the subquery cannot include a order clause.
> > how can i solve it.
> > can , am i cross post,
> > cos i dun know what should i post, i choose this newsgroup, because i
> am
> > using orcale...
> > Ivan Bajon <iba_at_no_spam_at_post1.tele.dk> wrote in message
> > news:832u1l$3g0$1_at_news.inet.tele.dk...
> > > select a, b, c from your_table
> > > where rownum < 4
> > > order by b;
> > >
> > > hth,
> > > Ivan Bajon, ocp
> > >
> > >
> > >
> > > Zenith <jackyleo_at_attmysite.com> wrote in message
> > > news:830rsb$jae$1_at_m5.att.net.hk...
> > > > I have learning sqlplus currently in a Oracle 7, sqlplus.
> > > > how can I list out the records in ascending order of one field?
> > > > if it can be done,
> > > > and how can i just selected the first three rows? with the use of
> a
> > > > function?
> > > > or i can only do this by a very long sql statment?
> > > >
> > > >
> > >
> > >
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Dec 15 1999 - 14:27:08 CST