From Teresita.Castro@s-martmx.com Wed, 11 Jun 2003 11:10:55 -0700 From: "Teresita Castro" Date: Wed, 11 Jun 2003 11:10:55 -0700 Subject: RE: How to put a TOP 1 in a select Message-ID: MIME-Version: 1.0 Content-Type: text/plain I run the next instruction:     select a.item, a.location, a.trans_date, b.can_vta from ( select item, location, trans_date,        rank() over (partition by item, location order by quantity) the_rank from ictrans a where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57') a INNER JOIN ( select item, location, sum(quantity) as can_vta from ictrans where company = 2000 and trans_date between (current_date-14) and current_date and doc_type = 'IS' and reason_code = 'VTCL' and substr(item,2,2) = '57' group by item, location ) b on a.item=b.item and a.location=b.location and a.the_rank=1   Information: item          Date     Location Quantity ======================================= 0570018  5/29/2003 12:00:00 AM TJU02 -40570018  5/31/2003 12:00:00 AM TJU02 -30570018  5/30/2003 12:00:00 AM TJU02 -20570018  6/2/2003 12:00:00 AM  TJU02 -20570018  6/3/2003 12:00:00 AM  TJU02 -20570018  6/1/2003 12:00:00 AM  TJU02 -10570018  5/30/2003 12:00:00 AM TJU24 -60570018  6/1/2003 12:00:00 AM  TJU24 -60570018  6/2/2003 12:00:00 AM  TJU24 -50570018  6/3/2003 12:00:00 AM  TJU24 -30570018  5/31/2003 12:00:00 AM TJU24 -20570018  6/9/2003 12:00:00 AM  TJU31 -4   Results: Item          Date     Location Quantity ========================================== 0570018    5/29/2003 12:00:00 AM   TJU02  -14   0570018   6/1/2003 12:00:00 AM  TJU24  -22   0570018  5/30/2003 12:00:00 AM TJU24  -22   0570018    6/9/2003 12:00:00 AM     TJU31   -4       The problem is that when we have an item that sold two or more days the same quantity and it is the biggest quantity it returns more that one row per item. This query is execute in 28 seconds     >>> [EMAIL PROTECTED] 06/10/03 09:24PM >>>There may be different ways to write this query  - otherwise you need tolook at tuning this query.  Four minutes for a single product in a 5million row table doesn't sound really good but I guess it depends onhardware.Have a look at the explain plan for the query - you have a lot of selectioncriteria on the ICTRANS table.  If one (or a group) of those criteria isvery restrictive then index (and analyze) that column(s) to see theperformance gain.I've had a go at writing this as an analytical query.  The syntax may beincorrect since I don't have anything convenient to test it against.  Theremay even be syntax errors - it was simply typed in without being executed.Good luck!!!select a.item, a.location, a.trans_date, sum(b.quantity) from (select item, location, trans_date       rank() over (partition by item, location order by quantity desc)the_rankfrom ictranswhere company = 2000 and trans_date between (current_date-14) andcurrent_dateand doc_type = 'IS' and reason_code = 'VTCL'and substr(item,2,2) = '57') a,ictrans bwhere a.the_rank = 1and a.item = b.itemand a.location = b.locationand b.company = 2000 and b.trans_date between (current_date-14) andcurrent_dateand b.doc_type = 'IS' and b.reason_code = 'VTCL'and substr(b.item,2,2) = '57')                                                                                                                                                              "Teresita Castro"                                                                                                                       <[EMAIL PROTECTED]        To:       Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>                                        martmx.com>                cc:                                                                                                          Sent by:                   Subject:  RE: How to put a TOP 1 in a select                                                                 [EMAIL PROTECTED]                                                                                                                     om                                                                                                                                                                                                                                                                                                                                                                                                                      11/06/2003 11:59                                                                                                                        Please respond to                                                                                                                       ORACLE-L                                                                                                                                                                                                                                                                                                                                                                                          This is an example of the information.I forgot to mention that in each company we have supermarkets called in thesystem Locations. So when I do this query I have to return per item thetotal of sales in the las two week, the day that we sale more persupermarketItem  trans_date       Quantity        Location========================================0570004    5/29/2003 12:00:00 AM -1 TJU020570004    6/3/2003   12:00:00 AM -1 TJU240570004    6/9/2003   12:00:00 AM -1 TJU310570006    5/28/2003 12:00:00 AM -1 TJU240570010    6/3/2003   12:00:00 AM -1 TJU020570010    5/30/2003  12:00:00 AM -1 TJU240570017    6/3/2003   12:00:00 AM -1 TJU240570018    5/29/2003 12:00:00 AM -4 TJU020570018    5/31/2003 12:00:00 AM -3 TJU020570018    5/28/2003 12:00:00 AM -2 TJU020570018    5/30/2003 12:00:00 AM -2 TJU020570018    6/3/2003 12:00:00 AM -2 TJU020570018    6/2/2003 12:00:00 AM -2 TJU020570018    6/1/2003 12:00:00 AM -1 TJU020570018    5/30/2003 12:00:00 AM -6 TJU240570018    6/1/2003 12:00:00 AM -6 TJU240570018    6/2/2003 12:00:00 AM -5 TJU240570018    6/3/2003 12:00:00 AM -3 TJU240570018    5/31/2003 12:00:00 AM -2 TJU240570018    5/28/2003 12:00:00 AM -1 TJU240570018    6/9/2003 12:00:00 AM -4 TJU310570019    6/2/2003 12:00:00 AM -3 TJU240570019    5/28/2003 12:00:00 AM -1 TJU240570019    6/9/2003 12:00:00 AM -1 TJU310570020    6/3/2003 12:00:00 AM -2 TJU020570020    5/31/2003 12:00:00 AM -1 TJU020570020    6/2/2003 12:00:00 AM -1 TJU020570020    6/1/2003 12:00:00 AM -1 TJU24And this should be the resultItem  trans_date      sum( Quantity )       Location========================================0570004    5/29/2003 12:00:00 AM -1 TJU020570004    6/3/2003   12:00:00 AM -1 TJU240570004    6/9/2003   12:00:00 AM -1 TJU310570006    5/28/2003 12:00:00 AM -1 TJU240570010    6/3/2003   12:00:00 AM -1 TJU020570010    5/30/2003  12:00:00 AM -1 TJU240570017    6/3/2003   12:00:00 AM -1 TJU240570018    5/29/2003 12:00:00 AM -16 TJU020570018    5/30/2003 12:00:00 AM (or 6/1/2003 12:00:00 AM ) -23 TJU240570018    6/9/2003 12:00:00 AM -4 TJU310570019    6/2/2003 12:00:00 AM -4 TJU240570019    6/9/2003 12:00:00 AM -1 TJU310570020    6/3/2003 12:00:00 AM -4 TJU020570020    6/1/2003 12:00:00 AM -1 TJU24I have a question I run this query:SELECT distinct a.ITEM, a.TRANS_DATE,a.LOCATION,       b.tot_QUANTITYFROM   ICTRANS a,       (       SELECT ITEM, LOCATION,SUM(QUANTITY) tot_QUANTITY, MAX(QUANTITY)max_QUANTITY       FROM   ICTRANS WHERE  COMPANY = 2000      -- and LOCATION='TJU02'       AND    TRANS_DATE  BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE       AND    DOC_TYPE = 'IS' AND    REASON_CODE = 'VTCL'       AND    SUBSTR(ITEM,2,2) = '57'       GROUP  BY ITEM ,LOCATION       ) bWHERE  a.ITEM='0570018' AND a.ITEM = b.ITEMAND    a.QUANTITY  = b.max_QUANTITYAND a.LOCATION=b.LOCATIONAND    a.TRANS_DATE  BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE;Just for 1 item and afther 4:12 minutes I have the results, the tableICTRANS have 4,628,226 rows, that is normal?When I tried to run the instruccion with out the item='0570018' it neverends.>>> [EMAIL PROTECTED] 06/10/03 05:29PM >>>Your query returns the maximum quantity (and associated date) for a singleICTRANS entry.  If there are multiple entries per day then the logic is alot more complex - but certainly achievable.  Since we don't know how datais stored in the table though the query below MAY be valid.                      "Chelur, Jayadas                      {PBSG}"                  To:       Multiplerecipients of list ORACLE-L <[EMAIL PROTECTED]>                      <[EMAIL PROTECTED]        cc:                      epsi.com>                Subject:  RE: How to put aTOP 1 in a select                      Sent by:                      [EMAIL PROTECTED]                      .com                      11/06/2003 04:25                      Please respond to                      ORACLE-LThis query would give you the total quantity sold in thepast two weeks and the date on which maximum number wassold, for each item ...SELECT a.item,       a.tras_date AS max_sale_date,       b.tot_qty   AS tot_sale_qtyFROM   ICTRANS a,       (       SELECT item, SUM(qty) tot_qty, MAX(qty) max_qty       FROM   ICTRANS       WHERE  company          = 2000       AND    trans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE       AND    doc_type         = 'IS'       AND    reason_code      = 'VTCL'       AND    SUBSTR(item,2,2) = '57'       GROUP  BY item       ) bWHERE  a.item = b.itemAND    a.qty  = b.max_qtyAND    trans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE;<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>   Privileged/Confidential information may be contained in this message.          If you are not the addressee indicated in this message       (or responsible for delivery of the message to such person),            you may not copy or deliver this message to anyone.In such case, you should destroy this message and kindly notify the sender           by reply e-mail or by telephone on (61 3) 9612-6999.   Please advise immediately if you or your employer does not consent to                Internet e-mail for messages of this kind.        Opinions, conclusions and other information in this message              that do not relate to the official business of                         Transurban City Link Ltd         shall be understood as neither given nor endorsed by it.<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Mark Richard  INET: [EMAIL PROTECTED]Fat City Network Services    -- 858-538-5051 http://www.fatcity.comSan Diego, California        -- Mailing list and web hosting services---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing).