Home » SQL & PL/SQL » SQL & PL/SQL » how to find results of groups within groups without using analytic functions such as rank.
how to find results of groups within groups without using analytic functions such as rank. [message #323406] Wed, 28 May 2008 07:24 Go to next message
bhaktis
Messages: 1
Registered: August 2007
Junior Member
hi i want to find say sum of salaries of topmost 2 employees within each department only using group by and having clause.I mean to say I dont want to use rank function or any such analytic funbction.Can I do it.Please help.
Re: how to find results of groups within groups without using analytic functions such as rank. [message #323418 is a reply to message #323406] Wed, 28 May 2008 08:00 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Welcome to the forum. It's good that we know what you want by posting your question. But what you have not done is followed the forum guidelines. Spend some time in reading the forum guidelines. In brief this is what we would like to see from you.

a) Post a sample test case .
b) Post what you have tried so far and where you are stuck
c) Last but not least follow the guidelines mentioned in the forum.

For your question I will give you a clue. Try to write a query how to identify a second maximum salary within a given department. As far as I can see you don't need a having clause for this problem.

Happy learning sql.

Regards

Raj
Re: how to find results of groups within groups without using analytic functions such as rank. [message #323573 is a reply to message #323406] Wed, 28 May 2008 21:09 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
It is possible to do top-n queries in Oracle without using rank and dense_rank. But you will find the queries are complex and poor performers. I have to ask why you would want to do top-n without rank and dense_rank? Analytic functions were created to answer top-n queries (and 100s of other "grouping" queries). What makes you think you can do a better job of it than the analytics?

Anything you write will always be at least two of the following:

1) slower
2) complex
3) non-generic in nature

Additionally, by using analytics, you gain benefits over releases. As these analytic functions get smarter, so does your code. There have already been several performance enhancements to analytics between 8i and 11g. No recoding required to get the benefit.

I suppose if you really must do it yourself, then you best bet might be to write some plsql function to do it. I won't given you any such code as I think it is a bad idea and the best way for you to understand this is to try and write it yourself, but it can be done.

However, you are just creating a bad situation. Use the analytics, this is what they were meant for.

It would be interesting why you are asking for a solution that does not require them? Can you give us this detail?

Kevin
Previous Topic: nvl in where clause
Next Topic: package hangs
Goto Forum:
  


Current Time: Fri Dec 09 13:46:30 CST 2016

Total time taken to generate the page: 0.22167 seconds