Oracle SQL Query to Find out Nth Largest, Highest, Lowest, Smallest Values

It is a common question in interviews to write a query find out nth largest and smallest. Also it is a generic requirement to find out nth values from a group of records.

Query Syntax for Nth Largest
 select * from <Table Name> a where &nthLarge =
 (select count(distinct <Column Name>)
  from <Table Name>  where <Column Name> >= a.<column Name>)

Query Syntax for Nth Smallest
select * from <Table Name> a where &nthSmall =
     (select count(distinct <Column Name>)
     from <Table Name>  where <Column Name> <=a.<Column Name>);


Example:

SQL> select * from emp order by sal;

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Jain                                   10       4000
Rupal                                  10       5000
Hero                                   10       5500
Roshan                                 10       5500
Roshan                                 30       5500
Kiran                                  30       5500
John                                   20       6000
Sam                                    20       6500
Peter                                  30       6800
Riju                                   20       7000
King                                   30       7600
David                                  10

SQL> select * from emp a where &nthLarge =
  2  (select count(distinct sal) from emp where sal>=a.sal);
Enter value for nthlarge: 1
old   1: select * from emp a where &nthLarge =
new   1: select * from emp a where 1 =

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
King                                   30       7600

SQL> /
Enter value for nthlarge: 6
old   1: select * from emp a where &nthLarge =
new   1: select * from emp a where 6 =

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Hero                                   10       5500
Kiran                                  30       5500
Roshan                                 30       5500
Roshan                                 10       5500


SQL> select * from emp a where &nthSmall =
     (select count(distinct sal) from emp where sal<=a.sal);
Enter value for nthsmall: 1
old   1: select * from emp a where &nthSmall =
new   1: select * from emp a where 1 =

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Jain                                   10       4000

SQL> /
Enter value for nthsmall: 5
old   1: select * from emp a where &nthSmall =
new   1: select * from emp a where 5 =

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google