hi all,
i want to get the top 3 product that are the most sold, i want to use
in the where clause, rowNum <= 3 but i dont know what is the exact
syntax, my attempt is this:

select Prod_Code , sum(qty) from purch
group by Prod_Code
order by sum(qty) desc

and from this i want just the first 3, rowNum will do the thing in
where but would you please tell me how to imply it properly?

thanks.

Totti schrieb:
Put your query into an inline view.
http://www.oracle.com/technology/ora...o56asktom.html

Best regards

Maxim

On Jan 2, 6:12 pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
You can do something like this...

select *
from
(select Prod_Code , sum(qty) from purch
group by Prod_Code
order by sum(qty) desc)
where rownum <= 3

On Jan 3, 8:06*am, david <davidho... (AT) gmail (DOT) com> wrote:
SQL> --
SQL> -- Create purch table
SQL> --
SQL> create table purch (
2 cust_id varchar2(20),
3 prod_code varchar2(12),
4 purch_dt date,
5 qty number,
6 back_order varchar2(1) default 'N'
7 );

Table created.

SQL>
SQL> --
SQL> -- Load some data
SQL> --
SQL> insert all
2 into purch
3 (cust_id, prod_code, purch_dt, qty)
4 values ('A456TZ','XYZ123', sysdate-20, 4000)
5 into purch
6 (cust_id, prod_code, purch_dt, qty)
7 values ('A456TZ','XYZ123', sysdate-10, 4000)
8 into purch
9 (cust_id, prod_code, purch_dt, qty)
10 values ('A456TZ','XYZ124', sysdate-30, 4000)
11 into purch
12 (cust_id, prod_code, purch_dt, qty)
13 values ('A456TZ','XYZ133', sysdate-40, 4000)
14 into purch
15 (cust_id, prod_code, purch_dt, qty)
16 values ('A456TZ','XYZ123', sysdate-2, 4000)
17 into purch
18 (cust_id, prod_code, purch_dt, qty)
19 values ('A456TZ','XYZ123', sysdate, 4000)
20 into purch
21 (cust_id, prod_code, purch_dt, qty)
22 values ('A456TZ','XYZ123', sysdate-50, 4000)
23 into purch
24 (cust_id, prod_code, purch_dt, qty)
25 values ('A456TZ','XYZ126', sysdate-10, 4000)
26 into purch
27 (cust_id, prod_code, purch_dt, qty)
28 values ('A456TZ','XYZ126', sysdate-20, 4000)
29 into purch
30 (cust_id, prod_code, purch_dt, qty)
31 values ('A456TZ','XYZ163', sysdate-20, 4000)
32 into purch
33 (cust_id, prod_code, purch_dt, qty)
34 values ('A456TZ','XYZ123', sysdate-20, 4000)
35 into purch
36 (cust_id, prod_code, purch_dt, qty)
37 values ('A456TZ','XYZ623', sysdate-20, 4000)
38 into purch
39 (cust_id, prod_code, purch_dt, qty)
40 values ('A456TZ','XYZ123', sysdate-20, 4000)
41 into purch
42 (cust_id, prod_code, purch_dt, qty)
43 values ('A456TZ','XYZ153', sysdate-20, 4000)
44 into purch
45 (cust_id, prod_code, purch_dt, qty)
46 values ('A456TZ','XYZ523', sysdate-20, 4000)
47 into purch
48 (cust_id, prod_code, purch_dt, qty)
49 values ('A456TZ','XYZ123', sysdate-20, 4000)
50 into purch
51 (cust_id, prod_code, purch_dt, qty)
52 values ('A456TZ','XYZ123', sysdate-20, 4000)
53 into purch
54 (cust_id, prod_code, purch_dt, qty)
55 values ('A456TZ','XYZ123', sysdate-20, 4000)
56 into purch
57 (cust_id, prod_code, purch_dt, qty)
58 values ('A456TZ','XYZ123', sysdate-20, 4000)
59 into purch
60 (cust_id, prod_code, purch_dt, qty)
61 values ('A456TZ','XYZ123', sysdate-20, 4000)
62 into purch
63 (cust_id, prod_code, purch_dt, qty)
64 values ('A456TZ','XYZ123', sysdate-20, 4000)
65 into purch
66 (cust_id, prod_code, purch_dt, qty)
67 values ('A456TZ','XYZ123', sysdate-20, 4000)
68 into purch
69 (cust_id, prod_code, purch_dt, qty)
70 values ('A456TZ','XYZ123', sysdate-20, 4000)
71 into purch
72 (cust_id, prod_code, purch_dt, qty)
73 values ('A456TZ','XYZ723', sysdate-20, 4000)
74 select * from dual;

24 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Query the totals
SQL> --
SQL> -- This query works ...
SQL> --
SQL> select *
2 from
3 (select Prod_Code , sum(qty) from purch
4 group by Prod_Code
5 order by sum(qty) desc)
6 where rownum <= 3 ;

PROD_CODE SUM(QTY)
------------ ----------
XYZ123 60000
XYZ126 8000
XYZ723 4000

SQL>
SQL> --
SQL> -- but I prefer this version as
SQL> -- it lists the columns returned
SQL> -- in the outer select and aliases
SQL> -- the sum(qty) field with a more
SQL> -- descriptive title (in my opinion)
SQL> --
SQL> select prod_code, ttl_sold
2 from
3 (select Prod_Code , sum(qty) ttl_sold
4 from purch
5 group by Prod_Code
6 order by sum(qty) desc)
7 where rownum <= 3 ;

PROD_CODE TTL_SOLD
------------ ----------
XYZ123 60000
XYZ126 8000
XYZ723 4000

SQL>
SQL> --
SQL> -- Drop the table
SQL> --
SQL> drop table purch;

Table dropped.

SQL>


David Fitzjarrell

Thank you all, you were all helpful;
David you solved it!
best regards