Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Performing Top-N Queries in Oracle-5 : Page 5


advertisement
Using RANK() to Obtain a Top-N Query
To obtain a top-N query, use RANK() in a subquery and then apply a filter condition outside the subquery:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, RANK() OVER (ORDER BY SAL Desc NULLS LAST) AS Emp_Rank FROM Emp ORDER BY SAL Desc NULLS LAST) WHERE Emp_Rank < 6;

Listing 6 shows the output of the above code.

Using the same technique, you can retrieve the bottom-five employees by salary:



SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, RANK() OVER (ORDER BY SAL ASC NULLS FIRST) AS Emp_Rank FROM Emp ORDER BY SAL ASC NULLS FIRST) WHERE Emp_Rank < 6;

Listing 7 shows the output of the above code.

Ranking functions can be used to operate within groups, too—that is, the rank value gets reset whenever the group changes. This is achieved with a PARTION BY subclause. Here is the syntax to retrieve the top employee by salary per manager group:

SELECT Empno, Ename, Job, Mgr, Hiredate, Sal FROM (SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, RANK() OVER (PARTITION BY MGR ORDER BY MGR, SAL DESC NULLS LAST) AS Emp_Rank FROM Emp ORDER BY MGR, SAL DESC NULLS LAST) WHERE Emp_Rank = 1;

Listing 8 shows the output of the above code.

As you can see, analytic functions are extremely useful in all types of analysis and computations, and they provide substantial SQL extensions to Oracle 8i.



Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date