SQL Server – Find Nth Highest or Lowest Value in Table

Find Nth Highest or Lowest Value in Table

This one is a tricky question. We all assume that this is not a big deal, but when this question is asked in the interviews, we try all the permutations and combinations of SELECT statements, but are never close to the answer.

There are many solutions to this problem. I have listed down some of the simple methods to find out Nth highest and Nth Lowest values in the table.

Method 1

USE AdventureWorks;

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A

ORDER BY UnitPrice DESC;

Method 2

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC)

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice)

ORDER BY UnitPrice DESC;

Method 3

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A;

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A;

Method 4

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC);

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice);

Method 5

— Finding unique third highest UnitPrice value from table

SELECT MAX(P1.UnitPrice) FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 <= (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice <= P2.UnitPrice);

Method 6

— Finding unique third highest UnitPrice value from table

SELECT DISTINCT P1.UnitPrice FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 = (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice<=P2.UnitPrice);

Hope you find this useful.

Good Luck!

Advertisements

2 responses to this post.

  1. Posted by Ashish on April 2, 2013 at 1:31 pm

    Its very very nice……. Thanks a Lot

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: