| 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
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
-----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'
and DOC_TYPE = 'IS'
and REASON_CODE = 'VTCL'
and substr(ITEM, 2,2) = '57'
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). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this messageReceived on Tue Jun 10 2003 - 18:36:00 CDT
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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).
 |
 |