How to retrieve a second highest salary from a table?
Note:Suppose salaries are in duplicate values
eg:
Name Sal
Malli 60000
Pandi 60000
Rudra 45000
Ravi 45000
Answers were Sorted based on User's Feedback
Answer / bibu
This IS HELP FULL 1ST ,2ND,3RD,4TH ANY HIGHEST SALARY U WRITE THIS QUERY.
SELECT * FROM Table_Name ALIAS_NAME 1
WHERE &N=(
SELECT COUNT(DISTINCT(WHICH COL U WANT THAT COL_NAME))
FROM Table_Name ALIAS_NAME 2
WHERE ALIAS1.CoL<=ALIAS2.Col
)
EX:
SELECT * FROM Emp E1
WHERE &N=(SELECT COUNT(DISTINCT(Sal))
FROM Emp E2
WHERE E1.Sal<=E2.Sal)
Is This Answer Correct ? | 11 Yes | 0 No |
Answer / mallinathabj
The Following queries to retrieve the second highest salary
SQL> select max(sal) from emp where sal<(select
max(distinct(sal)) from emp);
SQL> select min(sal) from(select distinct( sal) from emp order
by sal desc) where rownum<=2;
SQL> SELECT MAX(SAL) FROM EMP WHERE SAL NOT IN (SELECT
MAX(SAL) FROM EMP);
Is This Answer Correct ? | 12 Yes | 2 No |
Answer / praveenpinfo
Suppose table name is EMP.
SQL> Select distinct sal from emp e1 where 2=(select
count(distinct sal) from emp e2 where e1.sal<=e2.sal);
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / prativa mishra
select *
from
(select salary,dense_rank() over (order by salary) kk from table_name)
where kk=2
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / ashok
select sal from emp order by sal desc offset 1 rows fetch next 1 rows with ties;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / dewesh
With cte as (
Select *, Dense_rank() over (order by salary desc) as s from emp)
Select EmpName, Salary from cte where s =2
It has work. But duplicate value is not removed if name is different
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / welcomeashwin
--2 ND HIGHEST SALARY
SELECT MAX(SALARY) FROM HR.EMPLOYEES
WHERE SALARY < (SELECT MAX(DISTINCT(SALARY)) FROM
HR.EMPLOYEES);
--N TH HIGHEST SALARY
SELECT * FROM HR.EMPLOYEES EMP1
WHERE &N=(
SELECT COUNT(DISTINCT(SALARY))
FROM HR.EMPLOYEES EMP2
WHERE EMP1.SALARY<=EMP2.SALARY
);
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / sreeharibabu
SELECT id
FROM (select salary2.*, rownum rnum from
(select * from test ORDER BY id DESC) salary2
where rownum <=2 )
WHERE rnum >= 2;
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / rajat
SELECT * FROM (
SELECT RANK()OVER( ORDER BY SALARY DESC) NUM, A.* FROM RAJ A)
WHERE NUM=2;
Is This Answer Correct ? | 0 Yes | 1 No |
what is the difference between trigger and storedprocedures
What is oracle sql called?
Suppose a student column has two columns, name and marks. How to get name and marks of the top three students.
What are different methods to trace the pl/sql code?
how to get a list of all tables in a database? : Sql dba
What is sql profiler in oracle?
What is partition by in sql?
What is the difference between inner join and natural join?
ex: take one schema in that t1,t2,.....tn tables and you don't no the table name also. write a procedure if enter columns name then display the maching columns .otherwise display the unmatch columns.
What are basic techniques of indexing?
How to find 3rd highest salary of an employee from the employee table in sql?
Hi All, I am new to both this blog and technology. I was able to see a response for one of the questions on triggers as below. I would like to know why are we using " if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then" instead, can't we use " if sysdate = 'sunday' then". I can understand the use of "rtrim", but dont know y v r using to_char. I have seen this in many cases but did not get a convincible explaination. Please help me with this and do excuse if this question sounds silly. Thanks in advance...... create or replace trigger trg_sun before insert on <table name> begin if rtrim(to_char(sysdate,'day'))=rtrim('sunday') then raise_application_error(-20345,'no transaction in sunday'); end if; end trg_sun;