Nth Max Salary in Oracle

Nth max salary in Oracle

try this

select *
  from
  (
    select
        sal
          ,dense_rank() over (order by sal desc) ranking
    from   table
  )
  where ranking = 4 -- Replace 4 with any value of N

find nth highest salary in sql

SELECT * FROM EMPLOYEE e1
WHERE N-1 = 
           (SELECT COUNT(e2.ORIG_SALARY) FROM EMPLOYEE e2
                  WHERE e2.ORIG_SALARY > e1.ORIG_SALARY)

COUNT(e2.ORIG_SALARY) outputs the amount of entries in this column in the returned data set. The data set is all the salaries higher then the one in the row your main query returns.

WHERE N-1 = means that it will get the result where that count matches N-1

So that means you will get the a row of the table employee where there are N-1 higher salaries in the table, effectively giving you the row with the nth highest salary.

Keep in mind however, this is not perfect. For instance if you have multiple people with the same salary in the top 6, you don't get the nth highest salary, you get the nth highest sorted descending by salary.

Trying to find an Oracle query to find nth highest salary and among them one with highest experience

This query makes use of ROWNUM and MAX to find the right row. In the innermost sub-query the max experience is retreived for each salary level, ordered by salary descending, and then in the outer sub-query row numbers are added and this is joined with the original table to find the correct row(s)

SELECT s.name, s.salary, s.experience
FROM sal s
JOIN (SELECT s2.*, ROWNUM rnum
      FROM (SELECT salary, max(experience) AS m_exp
            FROM sal 
            GROUP BY salary
            ORDER BY salary DESC) s2) s3 ON s3.salary = s.salary AND 
                                            s3.m_exp = s.experience AND 
                                            rnum = 3 

Find out the nth-highest salary from table

you can use something like this.. this is what i have tested and then pasted here

SELECT *
FROM   tblname
WHERE  salary = (SELECT *
                 FROM   (SELECT *
                         FROM   (SELECT *
                                 FROM   (SELECT DISTINCT( salary )
                                         FROM   tblname
                                         ORDER  BY salary DESC) A
                                 WHERE  rownum <= nth) B
                         ORDER  BY salary ASC) C
                 WHERE  rownum <= 1) 

in place of 'tblname' give your table name and then in place nth give your desired nth highest salary that you want

you can see in the screen shot that it is working.

calculate highest nth salary using sql

SELECT *
FROM Employee Emp1
WHERE (N - 1) = (SELECT COUNT(DISTINCT(Emp2.orig_salary))
                FROM Employee Emp2  <--- cartesian product with same table
                WHERE emp2.orig_salary > emp1.orig_salary)  <---- but  do the cartesian product only if line of salary of emp 2 is greater than the current line of Emp1 'salary 

e.g assume there are only 3 lines in the table:

ID Name Birth Orig_Salary

2   John    15-JUL-97   2341    
3   Joe     25-JAN-86   4321    
4   Tom     13-SEP-06   5413 

the main query will look at the first line --> 2 John 15-JUL-97 2341 <---, and subquery will return 2 because the salaries 4321 (emp2.orig_salary) and 5413 (emp2.orig_salary) are greater than 2341 (emp1.orig_salary)

the main query will then look at the second line --> 3 Joe 25-JAN-86 4321 <---, and subquery will return 1 because the salaries 5413 (emp2.orig_salary) is greater than 2341 (emp1.orig_salary)

when i say subquery, it is the

=(SELECT COUNT(DISTINCT(Emp2.orig_salary))
                 FROM Employee Emp2  <--- cartesian product with same table
                WHERE emp2.orig_salary > emp1.orig_salary)

and the main query is

SELECT *
FROM Employee Emp1
WHERE 

the returned value from the subquery is then compare to the where condition n-1, if the condition is satisfied, then it retrieves the line.

How to find third or nᵗʰ maximum salary from salary table?

Use ROW_NUMBER(if you want a single) or DENSE_RANK(for all related rows):

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

---

Related Topics