find out the second highest salary?
Answers were Sorted based on User's Feedback
Answer / priyankur
@peter: your query is pretty cool but it wont work when
there are multiple salaries with same amount. I mean three
persons have same salary 24000, which is MAX sal in salary
table. In that case, below query will work.
select a.salary from employees a where 1 = (select count
(distinct b.salary) from employees b where
a.salary<b.salary)
Digit 1 is (n-1) where n is nth value. Here n is 2(Second
highest salary).
I would appreciate if anybody tried to break my above query
and explain how does this work.
Is This Answer Correct ? | 7 Yes | 8 No |
Answer / kamal
step 1=write a query for maximum salary.
step 2=make a sub query of step 1
step 2 a)let X==sleect all row from your table(employee)
b)let Y=select maximum salary from employee
NOw c)wrie X-Y
Syntax is:
select MAX(salary) from employeee((select * from
employee)-(selcect MAX(salary) from employee))
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / yaseen syed
select *
from
(select empno,ename,sal,rank() over(order by sal desc)
ranking from emp)
where ranking=2;
and u can find the nth highest sal by replacing 2 by your
number
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / shalini
SELECT ID,NAME,SAL,ROWNUM
FROM EMP
ORDER BY ID DESC
WHERE ROWNUM=2
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / anil
Guys try this to get 2nd highest salary
select max(salary) from employee where salary not in (select
max(salary) from employee)
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / yobu
select min(marks) from (SELECT sid FROM(SELECT sid FROM STUDENT ORDER BY SId desc) WHERE ROWNUM <=2);
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / ved
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / subash
select sal from emp e
where 2=(select count(distinct sal) from emp b where
b.sal>e.sal)
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / peter
SQL> select sal
2 from (select sal,
3 row_number() over
4 (order by sal desc ) rn
5 from emp )
6 where rn = 2;
SAL
----------
3000
SQL>
Is This Answer Correct ? | 13 Yes | 15 No |
What is max rowid in oracle?
What are the extensions used by oracle reports?
How to rename an existing table?
What is the effect of setting the value "CHOOSE" for OPTIMIZER_GOAL, parameter of the ALTER SESSION Command ?
What is the data pump export utility?
What is meant by raw datatype?
> CREATE OR REPLACE FUNCTION FACTORIAL_1(factstr varchar2 ) 2 RETURN NUMBER AS 3 new_str VARCHAR2(4000) := factstr||'*' ; 4 fact number := 1 ; 5 BEGIN 6 7 WHILE new_str IS NOT NULL 8 LOOP 9 fact := fact * TO_NUMBER(SUBSTR(new_str,1,INSTR(new_str,'*')-1)); 10 new_str := substr( new_str,INSTR(new_str,'*')+1); 11 END LOOP; 12 13 RETURN fact; 14 15 END; explanation Above program?
How to grant create session privilege to a user in oracle?
Can objects of the same schema reside in different tablespace?
How to use an explicit cursor without open statements?
Display those managers salary greater than the average salary of his employees?(Based on oracle standard Emp table)
1.how to extract the second highest salary from emp table having sal as a column which contains the salary of all employee of an organisation.