How to avoid the max funtion [message #444604] |
Mon, 22 February 2010 23:27  |
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
|
|
|
|
|