| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to put a TOP 1 in a select
use ROWNUM = 1 for instance
<FONT
face=Tahoma>-----Original Message-----From: Teresita Castro
[mailto:[EMAIL PROTECTED]Sent: Tuesday, June 10, 2003
5:50 PMTo: Multiple recipients of list ORACLE-LSubject:
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 :
<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<FONT face="Courier New" color=#000000 POINT-SIZE="9"> 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<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">
<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">
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">from<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> ICTRANS t
where COMPANY
<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">
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">
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.
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.
 |
 |