Home » SQL & PL/SQL » SQL & PL/SQL » Min and Max Rates with Corresponding Dates (Oracle 10G, Windows 2003)
Min and Max Rates with Corresponding Dates [message #572184] Fri, 07 December 2012 00:59 Go to next message
mmohsinaziz
Messages: 85
Registered: May 2012
Member
Dear All,
I am looking for a query to find out minimun and maximum rates of an item with corresponding dates.
Please help me to findout a query to get the required result.

Here is sample data

CREATE TABLE scott.item_rate
(
  code   VARCHAR2(3),
  rate   NUMBER(10,4),
  vdate  DATE
);

INSERT INTO item_rate
            (code, rate, vdate
            )
     VALUES ('001', 108.97, TO_DATE ('25-MAY-12', 'DD-MON-RR')
            );
INSERT INTO item_rate
            (code, rate, vdate
            )
     VALUES ('001', 108.97, TO_DATE ('07-APR-12', 'DD-MON-RR')
            );
INSERT INTO item_rate
            (code, rate, vdate
            )
     VALUES ('001', 105, TO_DATE ('05-DEC-12', 'DD-MON-RR')
            );
INSERT INTO item_rate
            (code, rate, vdate
            )
     VALUES ('001', 105, TO_DATE ('11-OCT-12', 'DD-MON-RR')
            );
INSERT INTO item_rate
            (code, rate, vdate
            )
     VALUES ('001', 91, TO_DATE ('02-JUL-10', 'DD-MON-RR')
            );
INSERT INTO item_rate
            (code, rate, vdate
            )
     VALUES ('001', 1, TO_DATE ('05-JUL-10', 'DD-MON-RR')
            );
INSERT INTO item_rate
            (code, rate, vdate
            )
     VALUES ('001', 1, TO_DATE ('31-AUG-10', 'DD-MON-RR')
            );



scott@orcl>SELECT * FROM ITEM_RATE;

COD RATE VDATE
--- ---------- ---------
001 108.97 25-MAY-12
001 108.97 07-APR-12
001 105 05-DEC-12
001 105 11-OCT-12
001 91 02-JUL-10
001 1 05-JUL-10
001 1 31-AUG-10

7 rows selected.

The required result is

CODE MIN_RATE MIN_DATE MAX_RATE MAX_DATE

001 1 05-JUL-10 108.97 25-MAY-12

Re: Min and Max Rates with Corresponding Dates [message #572187 is a reply to message #572184] Fri, 07 December 2012 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 58565
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's one solution:
SQL> with 
  2    data as (
  3      select code, rate, vdate, 
  4             row_number() over (partition by code order by rate desc, vdate desc) rn1,
  5             row_number() over (partition by code order by rate, vdate) rn2
  6      from item_rate
  7    )
  8  select code,
  9         max(decode(rn2, 1, rate)) min_rate,
 10         max(decode(rn2, 1, vdate)) min_rate_date,
 11         max(decode(rn1, 1, rate)) max_rate,
 12         max(decode(rn1, 1, vdate)) max_rate_date
 13  from data
 14  group by code
 15  /
COD   MIN_RATE MIN_RATE_DA   MAX_RATE MAX_RATE_DA
--- ---------- ----------- ---------- -----------
001          1 05-JUL-2010     108.97 25-MAY-2012

Regards
Michel
Re: Min and Max Rates with Corresponding Dates [message #572191 is a reply to message #572187] Fri, 07 December 2012 03:29 Go to previous messageGo to next message
saipradyumn
Messages: 178
Registered: October 2011
Location: Hyderabad
Senior Member


Hi Michel ,

Below query is also one of the solution. But from the perform prospective your is efficient

SQL> with data as
  2             (select se.code, min(rate) min_r, max(rate) max_r
  3                from scott.item_rate se
  4               group by se.code)
  5            select data.*, min(vdate) min_rate_date, max(vdate) max_rate_Date
  6              from data, scott.item_rate se2
  7             where data.code = se2.code and (min_r = rate or max_r = rate)
  8             group by data.code, data.min_r, data.max_r;
 
CODE      MIN_R      MAX_R MIN_RATE_DATE MAX_RATE_DATE
---- ---------- ---------- ------------- -------------
001           1     108.97 7/5/2010      5/25/2012
 



Please let me know if there any drawback about my query other than performance issue

Thanks
SaiPradyumn
Re: Min and Max Rates with Corresponding Dates [message #572195 is a reply to message #572191] Fri, 07 December 2012 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58565
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no other drawbacks I can see.

Regards
Michel
Re: Min and Max Rates with Corresponding Dates [message #572257 is a reply to message #572184] Sat, 08 December 2012 19:31 Go to previous messageGo to next message
shumail
Messages: 66
Registered: September 2012
Location: Canada
Member
I think you can get your desired resuly by using the below query.

WITH DATA AS
(
SELECT CODE,min(RATE),max(RATE),min(vdate),max(vdate)
FROM ITEM_RATE
group by code )
SELECT * From data;

Re: Min and Max Rates with Corresponding Dates [message #572261 is a reply to message #572257] Sun, 09 December 2012 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58565
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you have not the correct result.
The min/max dates you have are the min/max dates of the whole table when we want the min/max dates for the min/max rates.
Just execute your query with the test case and you will see your query does not return the correct result:
SQL> WITH DATA AS
  2  (
  3  SELECT CODE,min(RATE),max(RATE),min(vdate),max(vdate)
  4  FROM ITEM_RATE
  5  group by code )
  6  SELECT * From data;
COD  MIN(RATE)  MAX(RATE) MIN(VDATE)  MAX(VDATE)
--- ---------- ---------- ----------- -----------
001          1     108.97 02-JUL-2010 05-DEC-2012

Note that the subquery factoring (WITH) clause is useless in your query.

Regards
Michel

[Updated on: Tue, 11 December 2012 01:28]

Report message to a moderator

Re: Min and Max Rates with Corresponding Dates [message #572341 is a reply to message #572184] Mon, 10 December 2012 21:37 Go to previous messageGo to next message
shumail
Messages: 66
Registered: September 2012
Location: Canada
Member
Thanks for correcting me.
Re: Min and Max Rates with Corresponding Dates [message #572356 is a reply to message #572341] Tue, 11 December 2012 03:17 Go to previous messageGo to next message
sss111ind
Messages: 468
Registered: April 2012
Location: India
Senior Member

SELECT code, MIN (min_date)min_date, MIN (rate2)min_rate, MAX (max_date) max_date, MAX (rate1) max_rate
FROM (SELECT code, MIN (vdate) min_date, MAX (vdate) max_date,
MAX (rate) rate1, MIN (rate) rate2
FROM item_rate ir
WHERE rate IN ((SELECT MIN (rate)
FROM item_rate), (SELECT MAX (rate)
FROM item_rate))
GROUP BY code)
GROUP BY code;


Regards,
Nathan
Re: Min and Max Rates with Corresponding Dates [message #572357 is a reply to message #572356] Tue, 11 December 2012 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58565
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please INDENT your code, this is unreadable and so useless.

Regards
Michel
Re: Min and Max Rates with Corresponding Dates [message #572359 is a reply to message #572357] Tue, 11 December 2012 03:27 Go to previous messageGo to next message
sss111ind
Messages: 468
Registered: April 2012
Location: India
Senior Member

SELECT   code, MIN (min_date)min_date, MIN (rate2)min_rate, MAX (max_date) max_date, MAX (rate1) max_rate
    FROM (SELECT   code, MIN (vdate) min_date, MAX (vdate) max_date,
                   MAX (rate) rate1, MIN (rate) rate2
              FROM item_rate ir
             WHERE rate IN ((SELECT MIN (rate)
                               FROM item_rate), (SELECT MAX (rate)
                                                   FROM item_rate))
          GROUP BY code)
GROUP BY code;        



Regards,
Nathan
Re: Min and Max Rates with Corresponding Dates [message #572362 is a reply to message #572359] Tue, 11 December 2012 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 58565
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Does this give the correct result? Did you try it?
And what happens if there are several "CODE"?
Did you count how many times you scan the table with your "solution"?

Regards
Michel
Re: Min and Max Rates with Corresponding Dates [message #572365 is a reply to message #572362] Tue, 11 December 2012 03:47 Go to previous message
sss111ind
Messages: 468
Registered: April 2012
Location: India
Senior Member

Yes the above query is not working.

Regards,
Nathan
Previous Topic: mview is not refreshing
Next Topic: Version Wise Objects information
Goto Forum:
  


Current Time: Fri Jul 25 11:13:07 CDT 2014

Total time taken to generate the page: 0.07415 seconds