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 an Oracle index?
What is the difference between alert log file and trace file ?
What is private procedure oracle?
How do you find current date and time in oracle?
What is meant by an index?
I just want to maintain data like an employee can belongs to 3 or more departments . We can resolve this by using composite key but it avoids normalization rules. So Can anyone tell me how can I maintain data.
Can a property clause itself be based on a property clause ?
What is the difference between sharding and replication?
please explain.. DB architecture ...
I have table-A(1,2,3)& table-B(3,4,5).what is the different b/w below questions? A union all B? B union all A?
Explain the function of optimizer in oracle?
How to create a new tablespace in oracle?