Oracle: Get nth 10 record

Question

I want to get data from table in 5 member groups. How can I get nth five records( The nth group). I mean the records between (n-1)*5 and (n)*5.

I do not like to find my records in this below form, cause every time it runs 2 queries in my machine.

SELECT MAX(Salary) as 'Salary' from EmployeeDetails
where Salary NOT IN
(
SELECT TOP n-1 (SALARY) from EmployeeDetails ORDER BY Salary Desc
)

Note: I am new in Oracle and just asking you to give me a hint about this and not asking a complete code.

The LIMIT clause only works from 12c onwards in Oracle @opalenzuela. — Ben, Oct 21 '13 at 12:58

score 1 · Answer 1 · answered Oct 21 '13 at 13:12

This will work for Oracle 11 (and 10 and I think even 9):

SELECT MAX(Salary) AS MaxSal
FROM (
  SELECT
    Salary,
    RANK(Salary) OVER (ORDER BY Salary DESC) AS SalRank
  FROM EmployeeDetails
)
WHERE SalRank > 5

Note that if there's a tie for fifth place, this query will omit the top six or more salaries. For example:

Salary SalRank
------ -------
150000       1
145000       2
140000       3
135000       4
130000       5
130000       5
125000       7

The salary of 130,000 is tied for fifth place, so the top six salaries will have a rank <= 5.

If you want to include one of the 130,000 salaries (meaning you always want to exclude five and only five salaries), replace the RANK(Salary) in the query with ROW_NUMBER(Salary).

Oracle: Get nth 10 record

1 Answers1