Re: SQL question in sqlplus!

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 - 08:00:49 CST