RE: How to put a TOP 1 in a select

Tue, 10 Jun 2003 09:29:39 -0700

use ROWNUM = 1 for instance
-----Original Message-----
From: Teresita Castro [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 10, 2003 5:50 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: How to put a TOP 1 in a select

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.

**********************************************************************


Reply via email to