RE: How to put a TOP 1 in a select

From: Chelur, Jayadas {PBSG} <jayadas.chelur_at_pepsi.com>
Date: Tue, 10 Jun 2003 11:43:06 -0700
Message-ID: <F001.005AE6E3.20030610102500@fatcity.com>

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;

 -----Original Message-----
Sent: Tuesday, June 10, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L

Thanks!!
First let me explain more about the query I have a table ICTRANS that have all the inventary movements.  

I need to made a query that give each item from the guide 57, the information that I need is how much of each item we sell in the last two week ( from today), and what was the day that we sell more. The way I can know if it was a sell is because this condition must be true :reason_code = 'VTCL' and doc_type = 'IS'. IS means exit of inventary, that way the field Quantity have quantity with a negative sign ( ej: -30). If substr(item, 2,2) = '57' that mean that item is from the guide 57 ( patent medicine), company=2000 means that are from the same company we have one company per city.

This is what I have on mind in :  

select ITEM ,
sum(QUANTITY),
( select top 1 t2.TRANS_DATE
  from ICTRANS t2 where t2.ITEM = t.ITEM   where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate

        and DOC_TYPE = 'IS'
        and REASON_CODE = 'VTCL'
        and substr(ITEM, 2,2) = '57'

  order by t2.QUANTITY desc
)
from ICTRANS t
where COMPANY = 2000 and TRANS_DATE between (sysdate-14) and sysdate

        and DOC_TYPE = 'IS'
        and REASON_CODE = 'VTCL'
        and substr(ITEM, 2,2) = '57'

group by ITEM    

Thanks for your help I feel lost using Oracle.    

---

This electronic transmission is strictly confidential and intended solely

for the addressee. It may contain information which is covered by legal,

professional or other privilege. If you are not the intended addressee,

you must not disclose, copy or take any action in reliance of this

transmission. If you have received this transmission in error,

please notify the sender as soon as possible.

This footnote also confirms that this message has been swept

for computer viruses.

---

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chelur, Jayadas {PBSG}
  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).

Received on Tue Jun 10 2003 - 13:43:06 CDT