How to find third or nth maximum salary from salar

Question

How to find third or nth maximum salary from salary table(EmpID,EmpName,EmpSalary) in Optimized way?

Answer 1

By subquery:

SELECT salary from
(SELECT rownum ID, EmpSalary salary from
(SELECT DISTINCT EmpSalary from salary_table order by EmpSalary DESC)
where ID = nth)

Answer 2

Row Number :

SELECT Salary,EmpName
FROM
  (
   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
   FROM EMPLOYEE
   ) As A
WHERE A.RowNum IN (2,3)

Sub Query :

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )

Top Keyword :

SELECT TOP 1 salary
FROM (
      SELECT DISTINCT TOP n salary
      FROM employee
      ORDER BY salary DESC
      ) a
ORDER BY salary

Answer 3

Replace N with your Max Number

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 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

Answer 4

declare @maxNthSal as nvarchar(20)
SELECT TOP 3 @maxNthSal=GRN_NAME FROM GRN_HDR   ORDER BY GRN_NAME DESC
print @maxNthSal

Answer 5

another way to find last highest data based on date

SELECT A.JID,A.EntryDate,RefundDate,Comments,Refund, ActionBy FROM (
(select JID, Max(EntryDate) AS EntryDate from refundrequested GROUP BY JID) A 
Inner JOIN (SELECT JID,ENTRYDATE,refundDate,Comments,refund,ActionBy from refundrequested) B 
ON A.JID=B.JID AND A.EntryDate = B.EntryDate) 

Answer 6

Try this code :-

SELECT *
   FROM one one1
   WHERE ( n ) = ( SELECT COUNT( one2.salary )
                   FROM one one2
                   WHERE one2.salary >= one1.salary
                 )