Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to put a TOP 1 in a select

RE: How to put a TOP 1 in a select

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Wed, 11 Jun 2003 11:10:55 -0700
Message-ID: <F001.005AF5C6.20030611104944@fatcity.com>

I run the next instruction:
 

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

POINT-SIZE="9">select<FONT face="Courier New" color=#000000 POINT-SIZE="9"> a.item, a.location, a.trans_date, b.can_vta
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">from<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, trans_date,
<FONT face="Courier New" color=#000000

POINT-SIZE="9">       rank() over (<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">partition<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 <FONT face="Courier New" color=#0000ff 
POINT-SIZE="9">order<FONT face="Courier New" color=#000000 
POINT-SIZE="9"> <FONT face="Courier New" color=#0000ff POINT-SIZE="9">by
quantity)
the_rank
<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">from<FONT face="Courier New" color=#000000 POINT-SIZE="9"> ictrans a
<FONT
face="Courier New" color=#0000ff POINT-SIZE="9">where<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> 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"> (current_date-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=#000000

POINT-SIZE="9">current_date
<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=#000000 POINT-SIZE="9">) a
INNER JOIN (
<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) <FONT 
face="Courier New" color=#0000ff POINT-SIZE="9">as<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> can_vta

<FONT

face="Courier New" color=#0000ff POINT-SIZE="9">from<FONT face="Courier New" color=#000000 POINT-SIZE="9"> ictrans
<FONT
face="Courier New" color=#0000ff POINT-SIZE="9">where<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> 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"> (current_date-14) <FONT 
face="Courier New" color=#0000ff POINT-SIZE="9">and<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> current_date

<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=#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">on<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> a.item=b.item <FONT 
face="Courier New" color=#0000ff POINT-SIZE="9">and<FONT 
face="Courier New" color=#000000 POINT-SIZE="9"> a.location=b.location 
and<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> a.the_rank=1  
Information:
<FONT

face="Courier New">item         
Date     Location Quantity
<FONT
face="Courier New">=======================================
0570018  5/29/2003 12:00:00 
AM TJU02 -40570018  5/31/2003 12:00:00 

AM TJU02 -30570018  5/30/2003 12:00:00
AM TJU02 -20570018  6/2/2003 12:00:00 AM  TJU02 -20570018  6/3/2003 12:00:00
AM  TJU02 -20570018  6/1/2003 12:00:00 AM  TJU02 -10570018  5/30/2003
12:00:00 AM TJU24 -60570018  6/1/2003 12:00:00 AM 
TJU24 -60570018  6/2/2003 12:00:00 AM  
TJU24 -50570018  6/3/2003 12:00:00 AM 
 TJU24 -30570018  5/31/2003 12:00:00 

AM TJU24 -20570018  6/9/2003 12:00:00 AM  TJU31 -4
 
Results:

<FONT

face="Courier New">Item         
Date     Location Quantity
<FONT

face="Courier New">==========================================
0570018    5/29/2003 12:00:00 AM  
TJU02  -14   <FONT
color=#0000ff>0570018   6/1/2003 12:00:00 AM  TJU24 
-22   0570018  5/30/2003 12:00:00 AM TJU24  
-22   0570018    6/9/2003 
12:00:00 AM     TJU31   -4   

 
 
The problem is that when we have an item that sold two or more days the same quantity and it is the biggest quantity it returns more that one row per item.
This query is execute in 28 seconds
 
 
>>> [EMAIL PROTECTED] 06/10/03 09:24PM
>>>There may be different ways to write this query  - otherwise you need tolook at tuning this query.  Four minutes for a single product in a 5million row table doesn't sound really good but I guess it depends onhardware.Have a look at the explain plan for the query - you have a lot of selectioncriteria on the ICTRANS table.  If one (or a group) of those criteria isvery restrictive then index (and analyze) that column(s) to see theperformance gain.I've had a go at writing this as an analytical query.  The syntax may beincorrect since I don't have anything convenient to test it against.  Theremay even be syntax errors - it was simply typed in without being executed.Good luck!!!select a.item, a.location, a.trans_date, sum(b.quantity) from (select item, location, trans_date       rank() over
(partition by item, location order by quantity desc)the_rankfrom ictranswhere company = 2000 and trans_date between (current_date-14) andcurrent_dateand doc_type = 'IS' and reason_code = 'VTCL'and substr(item,2,2) = '57') a,ictrans bwhere a.the_rank = 1and a.item = b.itemand a.location = b.locationand b.company = 2000 and b.trans_date between (current_date-14) andcurrent_dateand b.doc_type = 'IS' and b.reason_code = 'VTCL'and substr(b.item,2,2) =

'57')                                                                                                                                        
                      

"Teresita
Castro"                                                                                                 
                      

<[EMAIL PROTECTED]       

To:       Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>                 
                      
martmx.com>                
cc:                                                                                    
                      

Sent
by:                   
Subject:  RE: How to put a TOP 1 in a 
select                                           
                      
[EMAIL PROTECTED]                                                                                               
                      
om                                                                                                                
                                                                                                                                        
                                                                                                                                        
                      

11/06/2003
11:59                                                                                                  
                      

Please respond
to                                                                                                 
                      
ORACLE-L                                                                                                          
                                                                                                                                        
                                                                                                                                        
This is an example of the information.I forgot to mention that in each company we have supermarkets called in thesystem Locations. So when I do this query I have to return per item thetotal of sales in the las two week, the day that we sale more persupermarketItem 
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 TJU24And this 
should be the resultItem 
trans_date      sum( 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 -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 TJU24I 
have a question I run this query:SELECT distinct a.ITEM, a.TRANS_DATE,a.LOCATION,      
b.tot_QUANTITYFROM   ICTRANS
a,      
(       SELECT ITEM, LOCATION,SUM(QUANTITY) tot_QUANTITY,
MAX(QUANTITY)max_QUANTITY      
FROM   ICTRANS WHERE  COMPANY =
2000      -- and
LOCATION='TJU02'       AND   
TRANS_DATE  BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE       AND   
DOC_TYPE = 'IS' AND    REASON_CODE =
'VTCL'       AND   
SUBSTR(ITEM,2,2) = '57'       GROUP  BY ITEM ,LOCATION       ) bWHERE 
a.ITEM='0570018' AND a.ITEM = b.ITEMAND    a.QUANTITY  = 
b.max_QUANTITYAND a.LOCATION=b.LOCATIONAND    
a.TRANS_DATE  BETWEEN (CURRENT_DATE-14) AND CURRENT_DATE;Just for 1 
item and afther 4:12 minutes I have the results, the tableICTRANS have 4,628,226 rows, that is normal?When I tried to run the instruccion with out the item='0570018' it neverends.>>>
[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:       Multiplerecipients of list ORACLE-L
<[EMAIL PROTECTED]>                     
<[EMAIL PROTECTED]       
cc:                      

epsi.com>               
Subject:  RE: How to put aTOP 1 in a
select                      

Sent
by:                      
[EMAIL PROTECTED]                      
.com                      

11/06/2003
04:25                      

Please respond
to                      

ORACLE-LThis 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;<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>   
Privileged/Confidential information may be contained in this message.          If you are
not the addressee indicated in this
message       (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: <A href="http://www.orafaq.net">http://www.orafaq.net-- Author: Mark Richard  INET: [EMAIL PROTECTED]Fat City Network Services    -- 858-538-5051 <A
href="http://www.fatcity.com">http://www.fatcity.comSan Diego, California        -- Mailing list and web hosting
services---------------------------------------------------------------------To 
REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from).  You mayalso send the HELP command for other information (like subscribing). Received on Wed Jun 11 2003 - 13:10:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US