how to find the n'th highest salary from emp ?
Answers were Sorted based on User's Feedback
Answer / brajesh kumar
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT
(DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal);
Is This Answer Correct ? | 15 Yes | 8 No |
Answer / venki
SELECT LEVEL,MAX(SAL) FROM EMP WHERE LEVEL=&LEVEL
CONNECT BY PRIOR SAL>SAL GROUP BY LEVEL;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / madhu
to get nth higest value
SELECT EMP_NAME,EMP_SALARY
FROM EMP_TABLE
ORDER BY EMP_SALARY DESC
LIMIT n-1, 1
this is for mysql server
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / ramaniranjan das
select top1 * from (select distinct top nth from table name
by column name ) alisa name of table order by column name
Example:-Create table tab_employee
(
Emp_id int,
Emp_name varchar(50),
Emp_salary money,
Emp_deg varchar(30),
emp_add varchar(100)
);
INSERT INTO tab_employee
VALUES ('1001','ANIL','18000','MANAGER','hyd')
Go
INSERT INTO tab_employee
VALUES ('1002','AKIL','6000','CLERK','Mub')
Go
INSERT INTO tab_employee
VALUES ('1003','VINOD','7000','SALESMAN','Bang' )
GO
INSERT INTO tab_employee
VALUES ('1004','VIKAS','8000','SALESMAN','BBSR' )
GO
INSERT INTO tab_employee
VALUES ('1005','SUNIL','15000','MANAGER','Orissa' )
select top 1 * from tab_employee where Emp_salary not in
(Select Distinct top 3 Emp_salary from tab_employee order
by Emp_salary Desc)order by Emp_salary Desc
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / bharath
select e1.* from emp e1
where &n=
(select count(distinct(*))
from emp e2
where e2.sal>e1.sal);
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / nusrath sultana
select * from emp
where &n=(select count(distinct(sal)) from emp b
where emp.sal<b.sal)
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / jaya u
SELECT A.EMPNAME ,A.SAL FROM EMP A
WHERE A.SAL = (SELECT DISTINCT (b.sal)
FROM EMP B WHERE ROWNUM = &N
ORDER BY B.SAL DESC );
Is This Answer Correct ? | 0 Yes | 5 No |
Answer / sunil panghal
select salary from emp where salary=(select max(salary)
from emp)
Is This Answer Correct ? | 6 Yes | 23 No |
Answer / brajesh kumar
SELECT *FROM EMP WHERE SAL=(SELECT MAX(SAL) FROM EMP)
Is This Answer Correct ? | 5 Yes | 25 No |
What is Oracle table?
How to use "startup" command to start default instance?
What does sharding mean?
Why do we use bulk collect in oracle?
what is Materialized view? What is a snapshot? what are the similarities and differences between Materialized views and snapshots?
What is null value in oracle?
Which Database is the best for the Security issue ? 1. DB2 2.SQL Server 3.MySQL 4.ORACLE
How would you change old and new values in an insert, delete and update triggers?
How do I call oracle stored procedures that take no parameters?
what is the difference between substr and instr function in oracle?
I want a table like, no name address addr1 addr2 So i want columns like addr1,addr2 under address column. Can one please answer me. Advance Thanks.
1) Does oracle have any table which contain all the exceptions and it's code internally?