select Nth highest salary by using rownum

Question Posted / siva kumar

4 Answers
21791 Views
Ford, TCS, Tech Mahindra, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

select Nth highest salary by using rownum..

SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM ( SELECT
DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<'&N');

ANS::

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
------ ---------- --------- ---------- --------- ----------
---------- ----------
7566 JONES MANAGER 7839 02-APR-81 2975
20

SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM ( SELECT
SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<'&N');

EMPNO ENAME JOB MGR HIREDATE SAL
COMM DEPTNO
------ ---------- --------- ---------- --------- ----------
---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7902 FORD ANALYST 7566 03-DEC-81 3000
20

Is This Answer Correct ?

8 Yes

0 No

select Nth highest salary by using rownum..

Answer / siva kumar

select rownum,empno,ename,sal from(select * from emp order by
sal desc)group by rownum,empno,ename,sal having rownum='&n'

Is This Answer Correct ?

6 Yes

2 No

select Nth highest salary by using rownum..

Answer / xxx

SELECT * FROM EMP WHERE N=(SELECT COUNT(DISTINCT SAL) FROM EMP
WHERE SAL>=e.SAL);

Is This Answer Correct ?

3 Yes

0 No

select Nth highest salary by using rownum..

Answer / welcomeashwin

SELECT * FROM HR.EMPLOYEES EMP1
WHERE &N=(
SELECT COUNT(DISTINCT(SALARY))
FROM HR.EMPLOYEES EMP2
WHERE EMP1.SALARY<=EMP2.SALARY
);

Is This Answer Correct ?

1 Yes

0 No

Post New Answer

More Database Management Interview Questions

What is data types in database?

0 Answers

What is view dbms?

0 Answers

What are different types of joins in sql?

0 Answers

what are the different kinds of indexing?

0 Answers

What is a database in layman terms?

0 Answers

What is pcbs (program communication blocks)?

0 Answers

Who invented schema theory?

0 Answers

Mention the types of join?

0 Answers

Explain the terms database and dbms. Also, mention the different types of dbms.

0 Answers

Define union all operator and union.

0 Answers

What is the use of INDEXES option in IMP command ?

2 Answers

Hi how to import oracle sequence in Informatica? Please write stored procedure code that will import oracle sequence in Informatica SP transformation as per below scenario Oracle table product list Pro_id, pro_name, price 101, LED Lights,200 102, 20 watt CFL Lights, 145 103, 30 watt CFL lights,155 Now a new flat file with new product list needs to be added to oracle table product list with oracle sequence flat file product list has two column pro_name, price 20 watt tube light ,45 30 watt tube light ,60

1 Answers TCS,

For more Database Management Interview Questions Click Here