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:

select 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=#0000ff POINT-SIZE="9">select item, location, trans_date,
 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"> by
quantity)
the_rank
<FONT

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

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