Home » SQL & PL/SQL » SQL & PL/SQL » using analytic function to solve my query
using analytic function to solve my query [message #276747] Fri, 26 October 2007 04:16 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
I have a table


create table instr(instrid number,histid number,statusid number)



data is


insert into instr values(20,121,4);
  insert into instr values(20,73,1);
  insert into instr values(20,65,1);
  insert into instr values(20,160,7);
  insert into instr values(20,22,1);
  insert into instr values(20,77,3);
  insert into instr values(20,29,2);
  insert into instr values(20,87,1);
  insert into instr values(20,58,1);
  insert into instr values(20,145,1);
  insert into instr values(20,90,3);




now, i want to fetch the statusid for the maximum value of
histid

in this case, answer is 7

i did this with subquery


 select statusid from instr where histid=(select max(histid) from instr)



can the same thing done with an analytic function?
i have never used them..can anyone help me with this one?
Re: using analytic function to solve my query [message #276748 is a reply to message #276747] Fri, 26 October 2007 04:25 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i think this will do



SELECT statusid 
FROM ( 
SELECT instrid, histid, statusid,
       MAX(histid) OVER (PARTITION BY instrid) job_max_sal  
       FROM instr e
) WHERE histid = job_max_sal;



any suggestions?
Re: using analytic function to solve my query [message #276751 is a reply to message #276747] Fri, 26 October 2007 04:36 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
i mean using analytic functions improves performance right?

any other solution for this? in a single query?

for a given instrid...need the statusid of the maximum
histid

Re: using analytic function to solve my query [message #276755 is a reply to message #276751] Fri, 26 October 2007 04:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
gautamvv wrote on Fri, 26 October 2007 11:36

i mean using analytic functions improves performance right?
Not always. They are faster if they are used correctly. In this case, I'd probably use a query like your initial one.

MHE

Edit: A table called "INSTR"? That's not the brightest idea, I'm afraid. I'd stay away from Oracle keywords if I were to name a table. Wink

[Updated on: Fri, 26 October 2007 04:50]

Report message to a moderator

Re: using analytic function to solve my query [message #276756 is a reply to message #276747] Fri, 26 October 2007 04:52 Go to previous messageGo to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
@Maaher

this is shocking, performance wise subquery is better...

can we user rank() here?

or can we achieve the same result with the help of a single
query?

Re: using analytic function to solve my query [message #276763 is a reply to message #276756] Fri, 26 October 2007 05:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
An analytic function will return a value for EVERY ROW in a given set. That is not what you want. You want a single value (the maximum histid) in a given set and use that.

Tom Kyte has written about it here.

MHE
Re: using analytic function to solve my query [message #276815 is a reply to message #276747] Fri, 26 October 2007 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For this question ("i want to fetch the statusid for the maximum value of histid"), if you have an index on histid better use aggregate max function, if you have not better use rank analytic function.

Regards
Michel
Re: using analytic function to solve my query [message #278535 is a reply to message #276815] Mon, 05 November 2007 00:12 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Or are you searching something like ..

 SELECT  distinct max(statusid) keep (dense_rank first order by histid desc )
  OVER (PARTITION BY instrid )  FROM instr e



Thumbs Up
Rajuvan
Re: using analytic function to solve my query [message #278546 is a reply to message #278535] Mon, 05 November 2007 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, I just give a general rule.

And here's another one: if you have to use distinct with analytic function then you wrongly use the function.
So your solution is not a good one (even if it gives the correct answer).

Regards
Michel
Re: using analytic function to solve my query [message #278603 is a reply to message #276747] Mon, 05 November 2007 02:36 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I agree that mine is a solution but not a good one . I suggested the answer just to put one more option for the OP . thats all Smile

Thumbs Up
Rajuvan
Re: using analytic function to solve my query [message #278613 is a reply to message #276747] Mon, 05 November 2007 02:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And If OP wants to return only statusid with hieghest histid ,

SELECT   max(statusid) keep (dense_rank first order by histid desc )  FROM  instr e 


may even be a good option as the He is specifically interseted in Analytice Functions. Neutral Neutral Neutral

Thumbs Up
Rajuvan
Re: using analytic function to solve my query [message #279089 is a reply to message #278613] Tue, 06 November 2007 20:59 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I think everything has been said that can be said already in this thread, but I'll throw my hat into the ring in support of some of the ideas presented:

- Rajuvan's last SQL using the KEEP extension to aggregate functions is the "right" solution. The question is an aggregate one, not an analytic one.

- Analytic functions are frequently slower than equivalent self-joins. Most analytic functions (and the KEEP aggregate variant) require a sort that is unavoidable, whereas joins (and sub-queries) can use a hash-join that scales more readily than a sort. If the query can be re-expressed as a self-join or sub-query that does NOT require a sort (such as this one), then it will probably be faster.

- I concur with the idea of indexing histid. Regardless of the size of the table, you can resolve the query with the equivalent work of a unique index scan.

Ross Leishman
Previous Topic: packages......
Next Topic: NLS_TIMESTAMP_FORMAT behavior
Goto Forum:
  


Current Time: Sat Dec 03 20:11:36 CST 2016

Total time taken to generate the page: 0.22845 seconds