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 the multiple records that you want to see returned.
And that will depend on the business needs. For example, you might want
the earlier date, so you could make the top line
<FONT face="Courier New" color=#0000ff
POINT-SIZE="9"> select<FONT face="Courier New"
color=#000000 POINT-SIZE="9"> a.item, a.location, min(a.trans_date),
b.can_vta
and add the appropriate group
by. Or you may want the latest date - only you can decide
that.
HTH,
Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415<FONT
color=#000080 size=2>
-----Original Message-----From: Teresita
Castro [mailto:[EMAIL PROTECTED] Sent: Wednesday, June
11, 2003 2:50 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: How to put a TOP 1 in a
select
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<FONT
face="Courier New" color=#000000 POINT-SIZE="9"> quantity)
<FONT face="Courier New" color=#000000
POINT-SIZE="9">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)
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) =
'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)
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) =
'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:42:14 CDT