While working on a project, I came across a requirement wherein I had to write a query to retrieve the Nth maximum (salary) of the records in a database. Sounds simple before starting to write the query but as and when progressed, I realized the need to find an efficient solution as compared to the ones normally used by all.

Many of us have come across some sort of a disarray in retrieving the nth maximum record in a given database. Most of the PL-SQL developers and DBAs are in continuous efforts to execute enormous queries in order to get the desired output. I have myself encountered several critical issues during PL-SQL programming on the projects which I work. But out of those, I am keen on sharing one such case in point which shows ever pertinent issue of finding out the nth maximum record in a database.

Nth maximum record:— It means retrieving the second or third or tenth maximum record for a given database.In my case, I had to retrieve nth maximum salary from a particular persons table i.e. employees of a company.
There may be several ways to retrieve the above output. But a few developers include ‘rownum’.   in their queries which  is probably not going to give the correct result.

Lets us see for instance,

A query normally written by most-
select * from (select persons.*, rownum as r1 from persons order by salary desc) where r1=2
The result of the above query is totally based on the way of records inserted into the table.
e.g. — The structure of the table persons is
CREATE TABLE PERSONS
(
P_ID INTEGER NOT NULL,
LASTNAME VARCHAR2 (255 BYTE) NOT NULL,
FIRSTNAME VARCHAR2 (255 BYTE) NOT NULL,
ADDRESS VARCHAR2 (255 BYTE),
CITY VARCHAR2 (255 BYTE),
SALARY NUMBER
)

Following are the insertion statements for the persons table:

- INSERT INTO PERSONS ( P_ID, LASTNAME, FIRSTNAME, ADDRESS, CITY, SALARY ) VALUES ( 3, ‘first’, ‘ankush5′, ‘city1′,   NULL, 500);

-INSERT INTO PERSONS ( P_ID, LASTNAME, FIRSTNAME, ADDRESS, CITY, SALARY ) VALUES ( 1, ‘second’, ‘ankush8′, ‘city2′, NULL, 80);

-INSERT INTO PERSONS ( P_ID, LASTNAME, FIRSTNAME, ADDRESS, CITY, SALARY ) VALUES ( 4, ‘third’, ‘ankush’, ‘city3′, NULL, 90);

-INSERT INTO PERSONS ( P_ID, LASTNAME, FIRSTNAME, ADDRESS, CITY, SALARY ) VALUES ( 2, ‘fourth’, ‘eankus’, ‘city4′, NULL, 600);
COMMIT;

If you keenly observe the above statements, there is no order of inserting the value of salary in persons table. In short the inserting values of salary are in random manner i.e not sorted. The person with salary of 500 comes first and 80 second and 600 in last as per the input in the database.

And, if a developer is to execute the query which includes rownum to retrieve the maximum second salary,
select * from (select persons.*, rownum as r1 from persons order by salary desc) where r1=2 order by salary desc.
Then, the output would be 80 because rownum for that row is 2. And the reason for the rownum =2 for this record just because it was inserted in 2nd place. So the logic of using rownum for retrieving the maximum 2nd salary is quite unjustifiable.

After many permutations and combinations, I finally arrived at what according to me is an accurate way of writing the query.That would be using dense_rank.

A query written using dense _rank:

SELECT * FROM (SELECT persons.*, DENSE_RANK() OVER (ORDER BY salary desc) s_dense_rank FROM persons ) WHERE s_dense_rank = 2

If I execute this query to retrieve the maximum second salary, I am most certain to get my desired output i.e. 500 even though it was inserted first in the table.

In Oracle/PL-SQL, the dense_rank function returns the rank of a row in a group of rows. The dense_rank function can be used two ways – as an Aggregate function or as an Analytic function. In my problem, I have used dense_rank as an Analytic function.

This is one of the ways I could arrive at. Please leave your comments and if you have any such similar situation and related solutions, please share them across to have a fruitful interaction.