Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • find_err.sql for finding errors from dba_errors. Oracle
  • useful dg links Oracle
  • Backup and Recovery Scenarios Oracle
  • Locktree.sql Oracle
  • DBMS_UTILITY PACKAGE Oracle
  • Pending Transaction Neighbors Script Oracle
  • Updated LCK.SQL file. Oracle
  • Directory wise folder wise space usage Linux/Unix
  • New OFA for 11g Oracle
  • Renaming Global Name GLOBAL_NAME Oracle
  • Good Doc 28-JUN-2006 Oracle
  • TRUNCATE table and disabling referential constraints. Oracle
  • Find Time Consuming SQL Statements in Oracle 10g Oracle
  • pvm_pre_change.sql Oracle
  • oracle tips… from http://www.bijoos.com/oracle/douknow.htm Oracle

Display the top 5 salaries for each department using single SQL

Posted on 03-May-2010 By Admin No Comments on Display the top 5 salaries for each department using single SQL

Ok, given the clarification I can show you a way to get this in 815 and before (slow on a

big table) and in 816 and up (fast with analytic functions!)

ops$tkyte@ORA817.US.ORACLE.COM> select * from t;

DEPTNO SAL

———- ———-

10 100

10 100

10 200

10 300

10 400

10 500

20 100

20 200

20 300

20 400

20 500

20 600

20 700

20 700

30 100

30 200

30 300

30 400

18 rows selected.

Thats my sample data. Now to get it in 815 and before:

ops$tkyte@ORA817.US.ORACLE.COM>

ops$tkyte@ORA817.US.ORACLE.COM>

ops$tkyte@ORA817.US.ORACLE.COM> select distinct *

2 from t t1

3 where 5 >= ( select count(distinct t2.sal)

4 from t t2

5 where t2.deptno = t1.deptno

6 and t2.sal >= t1.sal )

7 /

DEPTNO SAL

———- ———-

10 100

10 200

10 300

10 400

10 500

20 300

20 400

20 500

20 600

20 700

30 100

30 200

30 300

30 400

14 rows selected.

And now using a new feature of 816:

ops$tkyte@ORA817.US.ORACLE.COM> select distinct *

2 from ( select deptno, sal,

3 dense_rank() over ( partition by deptno order by sal desc ) rank

4 from t )

5 where rank <= 5
6 /

DEPTNO SAL RANK

———- ———- ———-

10 100 5

10 200 4

10 300 3

10 400 2

10 500 1

20 300 5

20 400 4

20 500 3

20 600 2

20 700 1

30 100 4

30 200 3

30 300 2

30 400 1

14 rows selected.

Oracle, SQL scripts

Post navigation

Previous Post: import-export with multiple files
Next Post: find_longsql.sql

Related Posts

  • findobj.sql Oracle
  • Vivek Tuning for Row Locks. Oracle
  • DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS Oracle
  • When to rebuld B-tree index Oracle
  • sid_wise_sql.sql Further explaination Oracle
  • SQL Tracker by SID sqltrackerbysid.sql Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Is It Recommended To Apply Patch Bundles When PSU Is Available? -ID 743554.1 Oracle
  • create database syntax Oracle
  • sess_server.sql Oracle
  • Jai Shree Ram Linux/Unix
  • Directory wise folder wise space usage Linux/Unix
  • move_arch_files.ksh Linux/Unix
  • xargs use Linux/Unix
  • 284785.1 How to check RAC Option is currently linked into the Oracle Binary Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme