RE: How to put a TOP 1 in a select

Teresita Castro Tue, 10 Jun 2003 18:22:25 -0700

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

========================================

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;

RE: How to put a TOP 1 in a select

Reply via email to