Re: How to put a TOP 1 in a select

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Tue, 10 Jun 2003 09:19:36 -0700
Message-ID: <F001.005AE530.20030610084943@fatcity.com>

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 :
 
<FONT face="Courier New" color=#0000ff

POINT-SIZE="9">select<FONT face="Courier New" color=#000000 POINT-SIZE="9"> ITEM ,
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">sum<FONT face="Courier New" color=#000000 POINT-SIZE="9">(QUANTITY), ( <FONT
face="Courier New" color=#0000ff POINT-SIZE="9">select<FONT face="Courier New" color=#000000 POINT-SIZE="9"> top 1 t2.TRANS_DATE

  <FONT
color=#0000ff POINT-SIZE="9">from
ICTRANS t2 where<FONT
color=#000000 POINT-SIZE="9"> t2.ITEM = t.ITEM  
where COMPANY =
2000 <FONT face="Courier New" color=#0000ff POINT-SIZE="9">and
TRANS_DATE <FONT face="Courier New" color=#0000ff

POINT-SIZE="9">between<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> (<FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">sysdate<FONT face="Courier New" color=#000000 
POINT-SIZE="9">-14) <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">and 

sysdate<FONT
face="Courier New" color=#000000 POINT-SIZE="9">

<FONT

face="Courier New" color=#0000ff
POINT-SIZE="9">        and<FONT

face="Courier New" color=#000000 POINT-SIZE="9"> DOC_TYPE = <FONT 
face="Courier New" color=#ff0000 POINT-SIZE="9">'IS'<FONT 
face="Courier New" color=#000000 POINT-SIZE="9">

<FONT

face="Courier New" color=#0000ff
POINT-SIZE="9">        and<FONT

face="Courier New" color=#000000 POINT-SIZE="9"> REASON_CODE = <FONT 
face="Courier New" color=#ff0000 POINT-SIZE="9">'VTCL'<FONT 
face="Courier New" color=#000000 POINT-SIZE="9">

<FONT

face="Courier New" color=#0000ff
POINT-SIZE="9">        and<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> substr(ITEM, 2,2) =
<FONT face="Courier New" color=#ff0000

POINT-SIZE="9">'57'
  <FONT
color=#0000ff POINT-SIZE="9">order
by<FONT color=#000000
POINT-SIZE="9"> t2.QUANTITY <FONT color=#0000ff POINT-SIZE="9">desc
)

<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">from<FONT face="Courier New" color=#000000 POINT-SIZE="9"> ICTRANS t where COMPANY =
2000 and<FONT color=#000000
POINT-SIZE="9"> TRANS_DATE <FONT color=#0000ff POINT-SIZE="9">between (<FONT
color=#0000ff POINT-SIZE="9">sysdate<FONT color=#000000 POINT-SIZE="9">-14) and<FONT
color=#000000 POINT-SIZE="9"> <FONT color=#0000ff POINT-SIZE="9">sysdate

<FONT

face="Courier New" color=#0000ff
POINT-SIZE="9">        and<FONT

face="Courier New" color=#000000 POINT-SIZE="9"> DOC_TYPE = <FONT 
face="Courier New" color=#ff0000 POINT-SIZE="9">'IS'<FONT 
face="Courier New" color=#000000 POINT-SIZE="9">

<FONT

face="Courier New" color=#0000ff
POINT-SIZE="9">        and<FONT

face="Courier New" color=#000000 POINT-SIZE="9"> REASON_CODE = <FONT 
face="Courier New" color=#ff0000 POINT-SIZE="9">'VTCL'<FONT 
face="Courier New" color=#000000 POINT-SIZE="9">

<FONT

face="Courier New" color=#0000ff
POINT-SIZE="9">        and<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> substr(ITEM, 2,2) =
<FONT face="Courier New" color=#ff0000

POINT-SIZE="9">'57'
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">group<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New" 
color=#0000ff POINT-SIZE="9">by<FONT face="Courier New" color=#000000 

POINT-SIZE="9"> ITEM
 
 
Thanks for your help I feel lost using Oracle.  
  Received on Tue Jun 10 2003 - 11:19:36 CDT