How can I select the record with the 2nd highest salary in database Oracle

Question

Suppose I have a table employee with id  user name  salary  How can I select the record with the 2nd highest salary in Oracle   I googled it  find this solution  is the following right    select sal from       select rownum n a   from           select distinct sal from emp order by sal desc  a  where n   2

User · Answer

I would suggest following two ways to implement this in Oracle.

Using Sub-query:

select distinct SALARY   
from EMPLOYEE e1  
where 1=(select count(DISTINCT e2.SALARY) from EMPLOYEE e2 where         
  e2.SALARY>e1.SALARY);

This is very simple query to get required output. However, this query is quite slow as each salary in inner query is compared with all distinct salaries.

Using DENSE_RANK():

select distinct SALARY   
from
  (
    select e1.*, DENSE_RANK () OVER (order by SALARY desc) as RN 
    from EMPLOYEE e
  ) E
 where E.RN=2;

This is very efficient query. It works well with DENSE_RANK() which assigns consecutive ranks unlike RANK() which assigns next rank depending on row number which is like olympic medaling.

Difference between RANK() and DENSE_RANK(): https://oracle-base.com/articles/misc/rank-dense-rank-first-last-analytic-functions

User · Answer

This query helps me every time for problems like this  Replace N with position    select   from       select        from  select   from TABLE NAME order by SALARY COLUMN desc       where rownum  lt  N       where SALARY COLUMN  lt   all                  select SALARY COLUMN                 from  select   from TABLE NAME order by SALARY COLUMN desc                  where rownum  lt  N

User · Answer

select Max Salary  as SecondHighestSalary from Employee where Salary not in   select max Salary  from Employee

User · Answer

RANK and DENSE RANK have already been suggested - depending on your requirements  you might also consider ROW NUMBER     select   from     select e    row number   over  order by sal desc  rn from emp e   where rn   2    The difference between RANK    DENSE RANK   and ROW NUMBER   boils down to    ROW NUMBER   always generates a unique ranking  if the ORDER BY clause cannot distinguish between two rows  it will still give them different rankings  randomly  RANK   and DENSE RANK   will give the same ranking to rows that cannot be distinguished by the ORDER BY clause DENSE RANK   will always generate a contiguous sequence of ranks  1 2 3       whereas RANK   will leave gaps after two or more rows with the same rank  think  Olympic Games   if two athletes win the gold medal  there is no second place  only third    So  if you only want one employee  even if there are several with the 2nd highest salary   I d recommend ROW NUMBER

User · Answer

select max Salary  from EmployeeTest where Salary  lt    select max Salary  from EmployeeTest       this will work for all DBs

User · Answer

select   from emp where sal     select sal from       select rownum n a sal from       select distinct sal from emp order by sal desc  a  where n   2     This is more optimum  it suits all scenarios

User · Answer

WITH records AS       SELECT  id  user name  salary              DENSE RANK   OVER  PARTITION BY id ORDER BY salary DESC  rn     FROM    tableName   SELECT  id  user name  salary FROM    records  WHERE   rn   2    DENSE RANK

User · Answer

Replace N with your Highest Number  SELECT   FROM Employee Emp1 WHERE  N-1      SELECT COUNT DISTINCT Emp2 Salary   FROM Employee Emp2 WHERE Emp2 Salary  gt  Emp1 Salary    Explanation  The query above can be quite confusing if you have not seen anything like it before     the inner query is what   s called a correlated sub-query because the inner query  the subquery  uses a value from the outer query  in this case the Emp1 table  in it   s WHERE clause   And Source  I have given the answer here  By the way I am flagging this Question as Duplicate

User · Answer

SELECT   FROM EMP WHERE SAL  SELECT MAX SAL  FROM EMP WHERE SAL lt  SELECT MAX SAL  FROM  EMP      OR  SELECT ENAME  SAL  FROM EMP  ORDER BY SAL DESC    OR  SELECT   FROM SELECT ENAME SAL  DENSE RANK    OVER PARTITION BY DEPTNO ORDER BY SAL DESC  R FROM EMP  WHERE R 2

User · Answer

Syntax it for Sql server  SELECT MAX Salary  as  Salary  from EmployeeDetails where Salary NOT IN   SELECT TOP n-1  SALARY  from EmployeeDetails ORDER BY Salary Desc     To get 2nd highest salary of employee then we need replace    n    with 2 our query like will be this  SELECT MAX Salary  as  Salary  from EmployeeDetails where Salary NOT IN   SELECT TOP 1  SALARY  from EmployeeDetails ORDER BY Salary Desc     3rd highest salary of employee  SELECT MAX Salary  as  Salary  from EmployeeDetails where Salary NOT IN   SELECT TOP 2  SALARY  from EmployeeDetails ORDER BY Salary Desc

User · Answer

select   FROM   select EmployeeID  Salary   dense rank   over  order by Salary DESC  ranking from Employee   WHERE ranking   2    dense rank   is used for the salary has to be same So it give the proper output instead of using rank

User · Answer

You can use two max function  Let s say get data of userid 10 and its 2nd highest salary from SALARY TBL   select max salary  from SALARY TBL where  userid 10 salary  lt  gt   select max salary  from SALARY TBL where userid 10

User · Answer

select   from emp where sal  select max sal  from emp where sal lt  select max sal  from emp     so in our emp table default provided by oracle  here is the output  EMPNO ENAME      JOB           MGR HIREDATE     SAL       COMM     DEPTNO      7698 BLAKE      MANAGER         7839 01-MAY-81       3000            30   7788 SCOTT      ANALYST         7566 19-APR-87       3000            20   7902 FORD       ANALYST         7566 03-DEC-81       3000            20   or just you want 2nd maximum salary to be displayed  select max sal  from emp where sal lt  select max sal  from emp    MAX SAL     3000

User · Answer

You should use something like this   SELECT   FROM  select salary2    rownum rnum from           select   from salary ORDER BY salary amount DESC  salary2   where rownum  lt   2   WHERE rnum  gt   2

User · Answer

I believe this will accomplish the same result  without a subquery or a ranking function   SELECT   FROM emp ORDER BY sal DESC LIMIT 1 OFFSET 2

User · Answer

select salary from EmployeeDetails order by salary desc limit 1 offset  n-1     If you want to find 2nd highest than replace n with that 2

User · Answer

This query works in SQL PLUS to find out the 2nd Highest Salary -  SELECT   FROM EMP WHERE SAL    SELECT MAX SAL  FROM EMP WHERE SAL  lt   SELECT MAX SAL  FROM EMP      This is double sub-query   I hope this helps you

User · Answer

If you re using Oracle 8   you can use the RANK   or DENSE RANK   functions like so   SELECT   FROM     SELECT some column            rank   over  order by your sort column desc  as row rank   t WHERE row rank   2

[sql] How can I select the record with the 2nd highest salary in database Oracle?

Examples related to sql

Examples related to oracle