Re: where clause help, please

From: <fitzjarrell_at_cox.net>
Date: Thu, 3 Jan 2008 06:56:49 -0800 (PST)
Message-ID: <f722de34-4516-45c8-8799-73edc9c1dad8@p31g2000hsd.googlegroups.com>

On Jan 3, 8:06 am, david <davidho..._at_gmail.com> wrote:
> On Jan 2, 6:12 pm, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
>
> > 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.
>
> 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

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 Received on Thu Jan 03 2008 - 08:56:49 CST