How to get the Nth Highest record

This type of scenario is most commonly asked in interviews where we need to find a record in the table whose ranks equals to N. Also, the output can change as per interviewer’s mood, so, we’ll see a few general scenarios where we can plug in the value of ‘N’ and voilà, a simple solution for How to get the Nth Highest record.

Let’s solve, How to get the Nth Highest record?

Let’s dive in, we will be using a table called GROCERY which has different products, expiration status, product’s price, and expiry date.

SELECT * FROM GROCERY;

Assigning ranks to the table Grocery on the basis of product’s rate for our reference.

SELECT * FROM (SELECT PRODUCT, IS_EXPIRED, RATE, EXPIRY_DATE, DENSE_RANK() OVER (ORDER BY RATE DESC) RNK FROM GROCERY) G1;

Now, Let’s get 6th highest value

Method 1:

SELECT * FROM (SELECT PRODUCT, IS_EXPIRED, RATE, EXPIRY_DATE, DENSE_RANK() OVER (ORDER BY RATE DESC) RNK FROM GROCERY) G1 WHERE RNK = 6

Method 2:

SELECT MIN(RATE) AS RATE FROM (SELECT DISTINCT RATE FROM GROCERY ORDER BY RATE DESC) WHERE ROWNUM <=6;

Bonus: 2nd Highest value without using rank or rownum

SELECT MAX(RATE) FROM GROCERY WHERE RATE NOT IN (SELECT MAX(RATE) FROM GROCERY);
How to concatenate multiple rows in Oracle? listagg concatenates multiple rows to a single string separating different values user defined delimiter.

Leave a Reply

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close