how to find the second highest salary from emp table?
Answers were Sorted based on User's Feedback
Answer / dev anand s
select salary from (
select salary, dense_rank() over(order by salary desc) rank from emp) where rank=2;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / rajkishore bagwan
simplest method ----
select salary
from employees
order by salary desc
offset n-1 rows fetch next 1 rows only
(replace n with any number it will give you the nth highest salary)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / hitesh
select top 1 salary from t1 where salary in(select top 2
salary from t1 order by salary desc)order by salary asc
Is This Answer Correct ? | 2 Yes | 3 No |
Answer / sabari
select max(salary ) from emp where salary<(select max
(salary)from emp )
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / sumit sharma
select sal from (select distinct(sal) from emp where sal is
NOT NULL order by sal dsc) where rownum = 2
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / priya
select sal from emp where sal <(select max(sal) from emo)
and rownum<2 order by desc
though rownum which is a pseudo column can be used for
comparisions with relational operators like > or < ,>=, <=
but equalto = may not work in most of the cases..
similarly rowid also.
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / nitin umale
SELECT *
FROM employees
WHERE salary =
( SELECT MIN ( r.salary)
FROM ( SELECT salary
FROM ( SELECT DISTINCT salary
FROM employees
ORDER BY NVL(salary,0) DESC
)
WHERE rownum<3) r
);
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / abhay
select max(sal) from emp where sal< (select max(sal) from
emp)
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / ravi
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n is the nth highest salary
Is This Answer Correct ? | 0 Yes | 1 No |
What is the use of partition by in sql?
How to add a column ‘salary’ to a table employee_details?
how to rename an existing column in a table? : Sql dba
What is the use of time stamp in select statement?
How to download oracle sql developer?
what is foreign key? : Sql dba
What is a full join?
What is benefit of creating memory optimized table?
Explain the usage of WHERE CURRENT OF clause in cursors ?
Is sqlite thread safe?
how to convert numeric values to character strings? : Sql dba
. have a tablle like this: cust acc ----------- a 1 b 2 b 3 c 4 c 5 c 6 I Want below o/p: cust acc --------------- a 1 b 2|3 c 4|5|6 Please any one can you have any ideas share me. I have urgent requirement. CUST ACC a dv b fg b bh c mk c cl c so result:- A B c dv fg mk bh cl so