Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid the max funtion (Oracle 10g)
How to avoid the max funtion [message #444604] Mon, 22 February 2010 23:27 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.

Please suggest another way where max can be avoided or a more tuned query .

create table tab1 (sapid number,
denid number);

create table tab2 (sap_pkid number ,sapid number,
denid number,
constraint pk_sapid primary key (sap_pkid ,sapid))

insert into tab1 values(1,50);
insert into tab1 values(2,50);

insert into tab2 values(5,1,50);
insert into tab2 values(10,1,50);
insert into tab2 values(15,1,50);
insert into tab2 values(20,1,50);
insert into tab2 values(25,2,50);
insert into tab2 values(30,2,50);

SELECT sap_pkid, tab2.sapid
  FROM tab1, tab2
 WHERE tab1.sapid = tab2.sapid
   AND tab1.denid = tab2.denid
   AND sap_pkid = (SELECT MAX (sap_pkid)
                     FROM tab2
                    WHERE tab1.sapid = tab2.sapid AND tab1.denid = tab2.denid)




Regards,
Mona

[Updated on: Mon, 22 February 2010 23:29]

Report message to a moderator

Re: How to avoid the max funtion [message #444608 is a reply to message #444604] Tue, 23 February 2010 00:06 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Instead of using correlated subquery, use MAX() in its Analytic form.

regards,
Delna
Re: How to avoid the max funtion [message #444613 is a reply to message #444604] Tue, 23 February 2010 00:29 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at RANK or DENSE_RANK functions.

Regards
Michel
Previous Topic: need to create colums from distinct rows in reporting
Next Topic: total count
Goto Forum:
  


Current Time: Sun Dec 04 10:31:09 CST 2016

Total time taken to generate the page: 0.12322 seconds