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 |
can a table has a column that has only view data and in other columns we can change data?
Please HELP me its urgent? If i want to EXPORT data from SQL server to ORACLE 11g then how can I replicate data from SQL to ORACLE?
Can a field be used in a report without it appearing in any data group ?
What is a sub query? What are its various types?
Can we protect our pl/sql source code?
What is a database schema in oracle?
How will you write a query to get a 5th rank student from a table student_report?
what is candidate key & super key
How to use attributes of the implicit cursor in oracle?
Give SQL Query to find the number words in a sentence ? ex: 'ram charan singh' then ans:3 Answer:select length(trim('ram charan singh')) - length (replace (trim ( 'ram charan singh'),' ','')) +1 from dual The above query working properly when space between the words is only one &similar But ,If the space between the words is nonuniform. Ex:'ram charan singh is good' ans:5 i am not getting this answer using above query.
How many categories of data types?
How to add a new column to an existing table with a default value?