- Script Name Nth Highest Salary
- Description Nth Highest Salary
- Category PL/SQL General
- Contributor Nth Highest Salary
- Created Thursday November 17, 2016

- Statement 1
`create table employee (emp_id varchar2(5), salary number)`

Table created. - Statement 2
`insert into employee values ('7878',10000)`

1 row(s) inserted. - Statement 3
`insert into employee values ('7879',20000)`

1 row(s) inserted. - Statement 4
`insert into employee values ('7880',20000)`

1 row(s) inserted. - Statement 5
`insert into employee values ('7881',30000)`

1 row(s) inserted. - Statement 6
`insert into employee values ('7882',40000)`

1 row(s) inserted. - Statement 7
`select * from employee`

EMP_ID SALARY 7878 10000 7879 20000 7880 20000 7881 30000 7882 40000

5 rows selected. - Statement 8
`select distinct salary from employee`

SALARY 10000 30000 40000 20000

4 rows selected. - Statement 9
`select * from (select distinct salary from employee ) where rownum=2`

no data found - Statement 10
`select salary, rownum from (select distinct salary from employee )`

SALARY ROWNUM 10000 1 30000 2 40000 3 20000 4

4 rows selected. - Statement 11
`select salary from (select salary, rownum "rown" from (select distinct salary from employee )) where rown=2`

ORA-00904: "ROWN": invalid identifier - Statement 12
`select * from (select salary, rownum "rown" from (select distinct salary from employee ))`

SALARY rown 10000 1 30000 2 40000 3 20000 4

4 rows selected. - Statement 13
`select * from (select salary, rownum "rown" from (select distinct salary from employee )) s where s.rown=2`

ORA-00904: "S"."ROWN": invalid identifier - Statement 14
`select * from (select salary, rownum "rown" from (select distinct salary from employee )) s where s.rown='2'`

ORA-00904: "S"."ROWN": invalid identifier - Statement 15
`select * from (select salary, rownum "rown" from (select distinct salary from employee ))`

SALARY rown 10000 1 30000 2 40000 3 20000 4

4 rows selected. - Statement 16
`select salary, rownum "rown" from (select distinct salary from employee ) where rown=2`

ORA-00904: "ROWN": invalid identifier - Statement 17
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum<=2`

SALARY rn 10000 1 20000 2

2 rows selected. - Statement 18
`select salary, rownum from (select distinct salary from employee )`

SALARY ROWNUM 10000 1 30000 2 40000 3 20000 4

4 rows selected. - Statement 19
`select salary, rownum "rn" from (select distinct salary from employee )`

SALARY rn 10000 1 30000 2 40000 3 20000 4

4 rows selected. - Statement 20
`select salary, rownum "rn" from (select distinct salary from employee ) where rn=1`

ORA-00904: "RN": invalid identifier - Statement 21
`select salary, rownum "rn" from (select distinct salary from employee )`

SALARY rn 10000 1 30000 2 40000 3 20000 4

4 rows selected. - Statement 22
`select salary, rownum "rn" from (select distinct salary from employee ) where rn<=2`

ORA-00904: "RN": invalid identifier - Statement 23
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum=2`

no data found - Statement 24
`select salary, rownum from (select distinct salary from employee ) where rownum=2`

no data found - Statement 25
`select salary, rownum from (select distinct salary from employee )`

SALARY ROWNUM 10000 1 30000 2 40000 3 20000 4

4 rows selected. - Statement 26
`select salary, rownum from (select distinct salary from employee ) where rownum<=2`

SALARY ROWNUM 10000 1 20000 2

2 rows selected. - Statement 27
`select salary, rownum from (select distinct salary from employee ) where rownum=2`

no data found - Statement 28
`select salary, rownum from (select distinct salary from employee ) where rownum<=2`

SALARY ROWNUM 10000 1 20000 2

2 rows selected. - Statement 29
`select salary, rownum from (select distinct salary from employee ) where rownum<=2 order by desc`

ORA-00936: missing expression - Statement 30
`select salary, rownum from (select distinct salary from employee ) where rownum<=2 order by desc rownum`

ORA-00936: missing expression - Statement 31
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum<=2 order by desc rownum`

ORA-00936: missing expression - Statement 32
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum<=2 order by desc rn`

ORA-00936: missing expression - Statement 33
`select * from (select distinct salary from employee ) where rownum=3`

no data found - Statement 34
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum<=2 order by desc salary`

ORA-00936: missing expression - Statement 35
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum<=2 order by salary desc`

SALARY rn 20000 2 10000 1

2 rows selected. - Statement 36
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum<=2 order by rn desc`

ORA-00904: "RN": invalid identifier - Statement 37
`select salary, rownum "rn" from (select distinct salary from employee ) where rownum<=2 order by rownum desc`

SALARY rn 20000 2 10000 1

2 rows selected. - Statement 38
`select salary, rownum from (select distinct salary from employee ) where rownum<=2 order by rownum desc limit 1`

ORA-00933: SQL command not properly ended - Statement 39
`select salary, rownum from (select distinct salary from employee ) where rownum<=2 order by rownum desc top 1`

ORA-00933: SQL command not properly ended - Statement 40
`select salary, rownum from (select distinct salary from employee ) where rownum<2 order by rownum desc top 1`

ORA-00933: SQL command not properly ended - Statement 41
`select salary, rownum from (select distinct salary from employee ) where rownum<2 order by rownum desc`

SALARY ROWNUM 10000 1 - Statement 42
`select salary, rownum from (select distinct salary from employee ) where rownum=2 order by rownum desc`

no data found - Statement 43
`select * from (select distinct salary from employee ) where rownum=2`

no data found - Statement 44
`select * from (select distinct salary from employee ) where rownum=1`

SALARY 10000 - Statement 45
`select * from (select distinct salary from employee ) where rownum<3`

SALARY 10000 20000

2 rows selected. - Statement 46
`select * from (select * from (select distinct salary from employee ) where rownum<=3 order by salary desc) where rownum=1`

SALARY 30000 - Statement 47
`select * from (select * from (select distinct salary from employee) where rownum<=4 order by salary desc) where rownum=1`

SALARY 40000 - Statement 48
`select * from (select * from (select distinct salary from employee) where rownum<=3 order by salary desc) where rownum=1`

SALARY 30000 - Statement 49
`select * from (select * from (select distinct salary from employee) where rownum<=1 order by salary desc) where rownum=1`

SALARY 10000 - Statement 50
`select * from (select * from (select distinct salary from employee) where rownum<=2 order by salary desc) where rownum=1`

SALARY 20000 - Statement 51
`select * from (select * from (select distinct salary from employee) where rownum<=4 order by salary desc) where rownum=1`

SALARY 40000