advertisements
_____________________________________________________________________________________________________________________
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 =
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
------------------------------ ---------- ----------
Sam 20 6500
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment