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