RE: How to put a TOP 1 in a select

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Tue, 10 Jun 2003 18:22:25 -0700
Message-ID: <F001.005AEC09.20030610175920@fatcity.com>

This is an example of the information.
I forgot to mention that in each company we have supermarkets called in the system Locations. So when I do this query I have to return per item the total of sales in the las two week, the day that we sale more per supermarket

Item trans_date
Quantity Location

0570004    5/29/2003 12:00:00
AM -1 TJU020570004    6/3/2003
12:00:00
AM -1 TJU240570004    6/9/2003
12:00:00
AM -1 TJU310570006    5/28/2003 12:00:00 AM -1 TJU240570010    6/3/2003
12:00:00 AM -1 TJU020570010    5/30/2003 12:00:00

AM -1 TJU240570017    6/3/2003   
12:00:00 AM -1 TJU240570018    5/29/2003 
12:00:00 AM -4 TJU020570018    5/31/2003 
12:00:00 AM -3 TJU020570018    5/28/2003 
12:00:00 AM -2 TJU020570018    5/30/2003 
12:00:00 AM -2 TJU020570018    6/3/2003 
12:00:00 AM -2 TJU020570018    6/2/2003 
12:00:00 AM -2 TJU020570018    6/1/2003 
12:00:00 AM -1 TJU020570018    5/30/2003 
12:00:00 AM -6 TJU240570018    6/1/2003 
12:00:00 AM -6 TJU240570018    6/2/2003 
12:00:00 AM -5 TJU240570018    6/3/2003 
12:00:00 AM -3 TJU240570018    5/31/2003 
12:00:00 AM -2 TJU240570018    5/28/2003 
12:00:00 AM -1 TJU240570018    6/9/2003 
12:00:00 AM -4 TJU310570019    6/2/2003 
12:00:00 AM -3 TJU240570019    5/28/2003 
12:00:00 AM -1 TJU240570019    6/9/2003 
12:00:00 AM -1 TJU310570020    6/3/2003 
12:00:00 AM -2 TJU020570020    5/31/2003 
12:00:00 AM -1 TJU020570020    6/2/2003 
12:00:00 AM -1 TJU020570020    6/1/2003

12:00:00 AM -1 TJU24
And this should be the result

Item trans_date sum(
Quantity ) Location

AM -1 TJU240570017    6/3/2003   
12:00:00 AM -1 TJU240570018    5/29/2003 
12:00:00 AM -16 TJU020570018    5/30/2003

12:00:00 AM (or 6/1/2003 12:00:00
AM ) -23 TJU240570018 6/9/2003

12:00:00 AM -4 TJU310570019    6/2/2003 
12:00:00 AM -4 TJU240570019    6/9/2003 
12:00:00 AM -1 TJU310570020    6/3/2003 
12:00:00 AM -4 TJU020570020    6/1/2003 
12:00:00 AM -1 TJU24

I have a question I run this query:

<FONT face="Courier New" color=#0000ff

POINT-SIZE="9">SELECT<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">distinct<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> a.ITEM, a.TRANS_DATE,a.LOCATION,

 b.tot_QUANTITY
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">FROM ICTRANS a,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">       (

 <FONT

face="Courier New" color=#0000ff POINT-SIZE="9">SELECT<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> ITEM, LOCATION,<FONT 
face="Courier New" color=#0000ff POINT-SIZE="9">SUM<FONT 
face="Courier New" color=#000000 POINT-SIZE="9">(QUANTITY) tot_QUANTITY,

MAX(QUANTITY) max_QUANTITY
 FROM ICTRANS WHERE COMPANY = 2000

 -- and LOCATION='TJU02'
<FONT

face="Courier New" color=#000000
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"> (CURRENT_DATE-14) <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">AND

CURRENT_DATE
 AND DOC_TYPE = 'IS' AND REASON_CODE = 'VTCL'<FONT face="Courier New" color=#000000

POINT-SIZE="9">

 AND SUBSTR(ITEM,2,2) = <FONT face="Courier New" color=#ff0000

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

 <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 ,LOCATION

 ) b
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">WHERE<FONT 
face="Courier New" color=#000000 POINT-SIZE="9">  a.ITEM=<FONT 
face="Courier New" color=#ff0000 POINT-SIZE="9">'0570018'<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> <FONT face="Courier New"

color=#0000ff POINT-SIZE="9">AND a.ITEM = b.ITEM
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">AND a.QUANTITY = b.max_QUANTITY
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">AND a.LOCATION=b.LOCATION
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">AND<FONT 
face="Courier New" color=#000000 POINT-SIZE="9">    
a.TRANS_DATE  <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">BETWEEN<FONT face="Courier New" color=#000000

POINT-SIZE="9"> (CURRENT_DATE-14) AND
CURRENT_DATE;

Just for 1 item and afther 4:12 minutes I have the results, the table ICTRANS have 4,628,226 rows, that is normal? When I tried to run the instruccion with out the item='0570018' it never ends.

>>> [EMAIL PROTECTED] 06/10/03 05:29PM
>>>Your query returns the maximum quantity (and associated date) for a singleICTRANS entry. If there are multiple entries per day then the logic is alot more complex - but certainly achievable. Since we don't know how datais 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 thepast two weeks and the date on which maximum number wassold, for each item ...SELECT a.item,
a.tras_date AS max_sale_date,
b.tot_qty   AS tot_sale_qtyFROM   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       ) bWHERE a.item =
b.itemAND    a.qty = b.max_qtyAND
trans_date BETWEEN TRUNC(SYSDATE-14) AND SYSDATE; Received on Tue Jun 10 2003 - 20:22:25 CDT