Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • shutdown linux Linux/Unix
  • find the files that are 1 day old. Linux/Unix
  • Insert cause enqueue locks Oracle
  • Passing from Unix to PLSQL using bind variables Linux/Unix
  • Drop database in Oracle 10g Oracle
  • checking redhat linux version Linux/Unix
  • Oracle Recommended Patches — Oracle Database ID 756671.1 Oracle
  • sid_wise_sql.sql Further explaination Oracle
  • Rman Notes -1 Oracle
  • How to hide author name in WordPress BLOG PHP/MYSQL/Wordpress
  • sqlnet.ora paramters Oracle
  • Find sort details from Db find_sort.sql Oracle
  • New Latest Param.sql for finding all hidden parameters also Oracle
  • Search and replace pattern Linux/Unix
  • ORACLE_SID in sqlplus 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

  • find_pk.sql /* Find Primary Key */ Oracle
  • Very clear article about oracle dataguard Oracle
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • Good notes for shared pool Oracle
  • How to find password change date for user Oracle
  • Goldengate Tutorial 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • How to find Unix server conguraiton including Server Model number and underlying disk sub-system ( On Solaris) Linux/Unix
  • Oracle Statspack survival Guide Oracle
  • kill all processes from specific user in solaris. Linux/Unix
  • Unix split command to split files Linux/Unix
  • Another Tuning Article for subheap of shared pool Oracle
  • Set Role explaination. Oracle
  • Oracle Release Explaination Oracle
  • USER_TABLES.Freelists Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme