Query to Find Nᵗʰ Max Value of a Column

Query to find nᵗʰ max value of a column

You could sort the column into descending format and then just obtain the value from the nth row.

EDIT::

Updated as per comment request. WARNING completely untested!

SELECT DOB FROM (SELECT DOB FROM USERS ORDER BY DOB DESC) WHERE ROWID = 6

Something like the above should work for Oracle ... you might have to get the syntax right first!

How to find n'th highest value of a column?

If it's a basic query, then just use LIMIT:

-- get the 4th highest salary
SELECT salary FROM tbl_salary
ORDER BY salary DESC
LIMIT 3,1

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

How to find the nth largest salary by department using SQLite

If this is homework and you have to use a correlated subquery then you must correlate also the department:

SELECT DISTINCT e1.department, e1.SAL 
FROM EMPLOYEE e1 
WHERE (N - 1) = (
  SELECT COUNT(DISTINCT e2.SAL) 
  FROM EMPLOYEE e2 
  WHERE e2.department = e1.department AND e2.SAL > e1.SAL 
);

A better solution would be to use DENSE_RANK() window function:

SELECT DISTINCT department, SAL
FROM ( 
  SELECT *, DENSE_RANK() OVER (PARTITION BY department ORDER BY SAL DESC) dr
  FROM EMPLOYEE 
)
WHERE dr = N;

SQL query to find Nth highest salary from a salary table

You can use a Common Table Expression (CTE) to derive the answer.

Let's say you have the following salaries in the table Salaries:

 EmployeeID  Salary
--------------------
     10101   50,000
     90140   35,000
     90151   72,000
     18010   39,000
     92389   80,000

We will use:

DECLARE @N int
SET @N = 3  -- Change the value here to pick a different salary rank

SELECT Salary
FROM (
    SELECT row_number() OVER (ORDER BY Salary DESC) as SalaryRank, Salary
    FROM Salaries
) as SalaryCTE
WHERE SalaryRank = @N

This will create a row number for each row after it has been sorted by the Salary in descending order, then retrieve the third row (which contains the third-highest record).

• SQL Fiddle

---

For those of you who don't want a CTE (or are stuck in SQL 2000):

[Note: this performs noticably worse than the above example; running them side-by-side with an exceution plans shows a query cost of 36% for the CTE and 64% for the subquery]:

SELECT TOP 1 Salary
FROM 
(
    SELECT TOP N Salary
    FROM Salaries
    ORDER BY Salary DESC
) SalarySubquery
ORDER BY Salary ASC

where N is defined by you.

SalarySubquery is the alias I have given to the subquery, or the query that is in parentheses.

What the subquery does is it selects the top N salaries (we'll say 3 in this case), and orders them by the greatest salary.

If we want to see the third-highest salary, the subquery would return:

 Salary
-----------
80,000
72,000
50,000

The outer query then selects the first salary from the subquery, except we're sorting it ascending this time, which sorts from smallest to largest, so 50,000 would be the first record sorted ascending.

As you can see, 50,000 is indeed the third-highest salary in the example.

I want to find the person with the 22nd highest salary

Use LIMIT, specifying both an offset and a row count.

To get the 22nd ranked person in order of highest salary, do:

SELECT person
FROM employee
ORDER BY salary DESC
LIMIT 21, 1

Notice the use of 21 here. This is because the offset of the initial row (1st highest salary) is actually 0. Therefore the 22nd highest salary will actually be an offset of 21 (the 21st row in 0-based counting, or "skip 21 rows").

---

To get the person(s) with the 22nd highest salary, you will need one more level of indirection. Try:

SELECT person
FROM employee
WHERE salary = (
    SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 21, 1
)

---

Related Topics