RE: How to put a TOP 1 in a select

Teresita Castro Wed, 11 Jun 2003 17:18:05 -0700

Yes, rank return duplicates, that why I have some items with two or more line per item.

>>> [EMAIL PROTECTED] 06/11/03 05:39PM >>>

That's a good approach to dealing with duplicates - wrap it up in another
query with a group by clause to remove the duplicates. It looks like we've
got your query down from "never" to around 30 seconds. Also since the
analytical function is doing a window sort and you will then be doing
another sort to remove duplicates the sort_area_size will have an impact on
performance - althought 30 seconds is probably good enough to not worry
about fiddling with this.

Just out of curiousity - did my query also return the duplicates? To be
honest, I'm not sure if RANK returns the same value for duplicates or not.
I guess that's something I should look into although I barely use this type
of query.

Regards,
      Mark.


                      "Carol Bristow"
                      <[EMAIL PROTECTED]        To:       Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
                      ra.com>                  cc:
                      Sent by:                 Subject: RE: How to put a TOP 1 in a select
                      [EMAIL PROTECTED]
                      .com


                      12/06/2003 05:25
                      Please respond to
                      ORACLE-L



That's always going to happen with something like a rank (or min/max).
You'll need to add some additional criteria to tell Oracle which of the
multiple records that you want to see returned. And that will depend on
the business needs. For example, you might want the earlier date, so you
could make the top line
    select a.item, a.location, min(a.trans_date), b.can_vta
and add the appropriate group by. Or you may want the latest date - only
you can decide that.

HTH,

Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415

-----Original Message-----
Sent: Wednesday, June 11, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L

      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 -4
      0570018 5/31/2003 12:00:00 AM TJU02 -3
      0570018 5/30/2003 12:00:00 AM TJU02 -2
      0570018 6/2/2003 12:00:00 AM TJU02 -2
      0570018 6/3/2003 12:00:00 AM TJU02 -2
      0570018 6/1/2003 12:00:00 AM TJU02 -1
      0570018 5/30/2003 12:00:00 AM TJU24 -6
      0570018 6/1/2003 12:00:00 AM TJU24 -6
      0570018 6/2/2003 12:00:00 AM TJU24 -5
      0570018 6/3/2003 12:00:00 AM TJU24 -3
      0570018 5/31/2003 12:00:00 AM TJU24 -2
      0570018 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
      to
      look at tuning this query. Four minutes for a single product in a 5
      million row table doesn't sound really good but I guess it depends on
      hardware.

      Have a look at the explain plan for the query - you have a lot of
      selection
      criteria on the ICTRANS table. If one (or a group) of those criteria
      is
      very restrictive then index (and analyze) that column(s) to see the
      performance gain.

      I've had a go at writing this as an analytical query. The syntax may
      be
      incorrect since I don't have anything convenient to test it against.
      There
      may 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_rank
      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') a,
      ictrans b
      where a.the_rank = 1
      and a.item = b.item
      and a.location = b.location
      and b.company = 2000 and b.trans_date between (current_date-14) and
      current_date
      and 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 the
      system Locations. So when I do this query I have to return per item
      the
      total of sales in the las two week, the day that we sale more per
      supermarket

      Item trans_date       Quantity        Location
      ========================================
      0570004    5/29/2003 12:00:00 AM -1 TJU02
      0570004    6/3/2003   12:00:00 AM -1 TJU24
      0570004    6/9/2003   12:00:00 AM -1 TJU31
      0570006    5/28/2003 12:00:00 AM -1 TJU24
      0570010    6/3/2003   12:00:00 AM -1 TJU02
      0570010    5/30/2003 12:00:00 AM -1 TJU24
      0570017    6/3/2003   12:00:00 AM -1 TJU24
      0570018    5/29/2003 12:00:00 AM -4 TJU02
      0570018    5/31/2003 12:00:00 AM -3 TJU02
      0570018    5/28/2003 12:00:00 AM -2 TJU02
      0570018    5/30/2003 12:00:00 AM -2 TJU02
      0570018    6/3/2003 12:00:00 AM -2 TJU02
      0570018    6/2/2003 12:00:00 AM -2 TJU02
      0570018    6/1/2003 12:00:00 AM -1 TJU02
      0570018    5/30/2003 12:00:00 AM -6 TJU24
      0570018    6/1/2003 12:00:00 AM -6 TJU24
      0570018    6/2/2003 12:00:00 AM -5 TJU24
      0570018    6/3/2003 12:00:00 AM -3 TJU24
      0570018    5/31/2003 12:00:00 AM -2 TJU24
      0570018    5/28/2003 12:00:00 AM -1 TJU24
      0570018    6/9/2003 12:00:00 AM -4 TJU31
      0570019    6/2/2003 12:00:00 AM -3 TJU24
      0570019    5/28/2003 12:00:00 AM -1 TJU24
      0570019    6/9/2003 12:00:00 AM -1 TJU31
      0570020    6/3/2003 12:00:00 AM -2 TJU02
      0570020    5/31/2003 12:00:00 AM -1 TJU02
      0570020    6/2/2003 12:00:00 AM -1 TJU02
      0570020    6/1/2003 12:00:00 AM -1 TJU24
      And this should be the result

      Item trans_date      sum( Quantity )       Location
      ========================================
      0570004    5/29/2003 12:00:00 AM -1 TJU02
      0570004    6/3/2003   12:00:00 AM -1 TJU24
      0570004    6/9/2003   12:00:00 AM -1 TJU31
      0570006    5/28/2003 12:00:00 AM -1 TJU24
      0570010    6/3/2003   12:00:00 AM -1 TJU02
      0570010    5/30/2003 12:00:00 AM -1 TJU24
      0570017    6/3/2003   12:00:00 AM -1 TJU24
      0570018    5/29/2003 12:00:00 AM -16 TJU02
      0570018    5/30/2003 12:00:00 AM (or 6/1/2003 12:00:00 AM ) -23 TJU24
      0570018    6/9/2003 12:00:00 AM -4 TJU31
      0570019    6/2/2003 12:00:00 AM -4 TJU24
      0570019    6/9/2003 12:00:00 AM -1 TJU31
      0570020    6/3/2003 12:00:00 AM -4 TJU02
      0570020    6/1/2003 12:00:00 AM -1 TJU24

      I have a question I run this query:

      SELECT distinct a.ITEM, a.TRANS_DATE,a.LOCATION,
             b.tot_QUANTITY
      FROM   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
             ) b
      WHERE a.ITEM='0570018' AND a.ITEM = b.ITEM
      AND    a.QUANTITY = b.max_QUANTITY
      AND a.LOCATION=b.LOCATION
      AND    a.TRANS_DATE BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE;

      Just for 1 item and afther 4:12 minutes I have the results, the table
      ICTRANS have 4,628,226 rows, that is normal?
      When I tried to run the instruccion with out the item='0570018' it
      never
      ends.

      >>> [EMAIL PROTECTED] 06/10/03 05:29PM >>>

      Your query returns the maximum quantity (and associated date) for a
      single
      ICTRANS entry. If there are multiple entries per day then the logic
      is a
      lot more complex - but certainly achievable. Since we don't know how
      data
      is stored in the table though the query below MAY be valid.

                            "Chelur, Jayadas

                            {PBSG}"                  To:       Multiple
      recipients of list ORACLE-L <[EMAIL PROTECTED]>
                            <[EMAIL PROTECTED]        cc:

                            epsi.com>                Subject: RE: How to
      put a
      TOP 1 in a select
                            Sent by:

                            [EMAIL PROTECTED]

                            .com

                            11/06/2003 04:25

                            Please respond to

                            ORACLE-L

      This query would give you the total quantity sold in the
      past two weeks and the date on which maximum number was
      sold, for each item ...

      SELECT a.item,
             a.tras_date AS max_sale_date,
             b.tot_qty   AS tot_sale_qty
      FROM   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
             ) b
      WHERE a.item = b.item
      AND    a.qty = b.max_qty
      AND    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.com
      San Diego, California        -- Mailing list and web hosting services
      ---------------------------------------------------------------------
      To REMOVE yourself from this mailing list, send an E-Mail message
      to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
      the message BODY, include a line containing: UNSUB ORACLE-L
      (or the name of mailing list you want to be removed from). You may
      also send the HELP command for other information (like subscribing).

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   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.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

RE: How to put a TOP 1 in a select

Reply via email to