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 17:18:05 -0700
Message-ID: <F001.005AFAD3.20030611165420@fatcity.com>

Yes, rank return duplicates, that why I have some items with two or more line per item.
>>> [EMAIL PROTECTED] 06/11/03 05:39PM
>>>That's a good approach to dealing with duplicates - wrap it up in anotherquery with a group by clause to remove the duplicates.  It looks like we'vegot your query down from "never" to around 30 seconds.  Also since theanalytical function is doing a window sort and you will then be doinganother sort to remove duplicates the sort_area_size will have an impact onperformance - althought 30 seconds is probably good enough to not worryabout fiddling with this.Just out of curiousity - did my query also return the duplicates?  To behonest, I'm not sure if RANK returns the same value for duplicates or not.I guess that's something I should look into although I barely use this typeof

query.Regards,      
Mark.                                                                                                                                      

                     
"Carol Bristow"                                                                                                
                     

<[EMAIL PROTECTED]       
To:       Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>                  

                     
ra.com>                  cc:                                                                                   
                     

Sent
by:                 
Subject:  RE: How to put a TOP 1 in a 
select                                           

                     
[EMAIL PROTECTED]                                                                                              
                     
.com                                                                                                                                                                                                                                                                                                                                                                                       
                     

12/06/2003
05:25                                                                                                

                     

Please respond
to                                                                                               

                     
ORACLE-L                                                                                                                                                                                                                                                                                                                                                                                   
That's always going to happen with something like a rank (or min/max).You'll need to add some additional criteria to tell Oracle which of themultiple records that you want to see returned.  And that will depend onthe business needs.  For example, you might want the earlier date, so youcould make the top line    select a.item, a.location, min(a.trans_date), b.can_vtaand add the appropriate group by.  Or you may want the latest date - onlyyou can decide that.HTH,Carol BristowDPRA Inc.1300 N 17th St Suite 950Rosslyn, VA 22209Work: 703-841-8025Fax: 703-524-9415-----Original Message-----Sent: Wednesday, June 11, 2003 2:50 PMTo: Multiple recipients of list
ORACLE-L      I run the next
instruction:      select a.item,
a.location, a.trans_date, b.can_vta      from (      select item, location,
trans_date,            
rank() over (partition by item, location order by quantity)     
the_rank      from ictrans
a      where company = 2000 and trans_date between (current_date-14) and     
current_date      and doc_type = 'IS' and reason_code = 'VTCL'      and substr(item,2,2) = '57') a      INNER
JOIN      (     
select item, location, sum(quantity) as
can_vta      from 
ictrans      where company = 2000 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      ) b     
on a.item=b.item and a.location=b.location and
a.the_rank=1      
Information:      
item          

Date     Location Quantity     
=======================================      
0570018  5/29/2003 12:00:00 AM TJU02 -4      
0570018  5/31/2003 12:00:00 AM TJU02 -3      
0570018  5/30/2003 12:00:00 AM TJU02 -2      0570018  6/2/2003 12:00:00 AM  TJU02
-2      0570018  6/3/2003 12:00:00 AM 
TJU02 -2      0570018  6/1/2003 12:00:00 AM  TJU02 -1      0570018  5/30/2003
12:00:00 AM TJU24 -6      0570018  6/1/2003 12:00:00 AM  TJU24 -6      0570018 
6/2/2003 12:00:00 AM  TJU24 -5     
0570018  6/3/2003 12:00:00 AM  TJU24
-3      0570018  5/31/2003 12:00:00 AM TJU24 -2      0570018  6/9/2003 12:00:00 AM 
TJU31 -4      
Results:      
Item          

Date     Location Quantity     
==========================================      
0570018    5/29/2003 12:00:00 AM   TJU02  -14      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      to      look at tuning this query.  Four minutes for a single product in a 5      million row table doesn't sound really good but I guess it depends on     
hardware.      Have a look at the explain plan for the query - you have a lot of     
selection      criteria on the ICTRANS table.  If one (or a group) of those criteria      is      very restrictive then index (and analyze) that column(s) to see the      performance gain.      I've had a go at writing this as an analytical query.  The syntax may     
be      incorrect since I don't have anything convenient to test it against.     
There      may 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_rank      from 
ictrans      where company = 2000 and trans_date 
between (current_date-14) and     
current_date      and doc_type = 'IS' and reason_code = 'VTCL'      and substr(item,2,2) = '57') a,      ictrans
b      where a.the_rank =
1      and a.item =
b.item      and a.location = 
b.location      and b.company = 2000 and 
b.trans_date between (current_date-14) and      
current_date      and 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
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 
TJU02      0570004    
6/3/2003   12:00:00 AM -1 TJU24      
0570004    6/9/2003   12:00:00 AM -1 

TJU31      0570006    5/28/2003
12:00:00 AM -1 TJU24      0570010   
6/3/2003   12:00:00 AM -1 TJU02      
0570010    5/30/2003  12:00:00 AM -1 
TJU24      0570017    
6/3/2003   12:00:00 AM -1 TJU24      
0570018    5/29/2003 12:00:00 AM -4 
TJU02      0570018    5/31/2003 

12:00:00 AM -3 TJU02      0570018   
5/28/2003 12:00:00 AM -2 TJU02     
0570018    5/30/2003 12:00:00 AM -2
TJU02      0570018    6/3/2003
12:00:00 AM -2 TJU02      0570018   
6/2/2003 12:00:00 AM -2 TJU02     
0570018    6/1/2003 12:00:00 AM -1
TJU02      0570018    5/30/2003
12:00:00 AM -6 TJU24      0570018   
6/1/2003 12:00:00 AM -6 TJU24     
0570018    6/2/2003 12:00:00 AM -5
TJU24      0570018    6/3/2003
12:00:00 AM -3 TJU24      0570018   
5/31/2003 12:00:00 AM -2 TJU24     
0570018    5/28/2003 12:00:00 AM -1
TJU24      0570018    6/9/2003
12:00:00 AM -4 TJU31      0570019   
6/2/2003 12:00:00 AM -3 TJU24     
0570019    5/28/2003 12:00:00 AM -1
TJU24      0570019    6/9/2003
12:00:00 AM -1 TJU31      0570020   
6/3/2003 12:00:00 AM -2 TJU02     
0570020    5/31/2003 12:00:00 AM -1
TJU02      0570020    6/2/2003
12:00:00 AM -1 TJU02      0570020   
6/1/2003 12:00:00 AM -1 TJU24      And this should be the result      Item 
trans_date      sum( Quantity
)       Location     
========================================      
0570004    5/29/2003 12:00:00 AM -1 
TJU02      0570004    
6/3/2003   12:00:00 AM -1 TJU24      
0570004    6/9/2003   12:00:00 AM -1 

TJU31      0570006    5/28/2003
12:00:00 AM -1 TJU24      0570010   
6/3/2003   12:00:00 AM -1 TJU02      
0570010    5/30/2003  12:00:00 AM -1 
TJU24      0570017    
6/3/2003   12:00:00 AM -1 TJU24      
0570018    5/29/2003 12:00:00 AM -16 
TJU02      0570018    5/30/2003 

12:00:00 AM (or 6/1/2003 12:00:00 AM ) -23 TJU24      0570018    6/9/2003
12:00:00 AM -4 TJU31      0570019   
6/2/2003 12:00:00 AM -4 TJU24     
0570019    6/9/2003 12:00:00 AM -1
TJU31      0570020    6/3/2003
12:00:00 AM -4 TJU02      0570020   
6/1/2003 12:00:00 AM -1 TJU24      I have a question I run this query:      SELECT distinct
a.ITEM, 
a.TRANS_DATE,a.LOCATION,             
b.tot_QUANTITY      FROM   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            
) b      WHERE  a.ITEM='0570018' AND a.ITEM = b.ITEM      AND    a.QUANTITY 
= b.max_QUANTITY      AND
a.LOCATION=b.LOCATION      AND   
a.TRANS_DATE  BETWEEN (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      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      WHERE  a.item = 

b.item      AND    a.qty  =
b.max_qty      AND    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.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
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 - 19:18:05 CDT

Original text of this message

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