SQL Query to find second highest value

Bineesh Viswanath

12y1.5k

Sir, I need your help to find the second highest value related to this query.

Here I writing query to find the customerName, ProductName and ProductPrice.

select A.customerName,B.productName,B.unitPrice from tbl_Customer as A
inner join tbl_custProductSales on A.customerId=tbl_custProductSales.customerId inner join
tbl_Product as B on B.productId=tbl_custProductSales.productId

Like this I want to get the second highest productPrice, customer and productName

My result would be:

customer product second_highest_productRate
-----------------------------------------------------

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi12y ago
hi
Try...
select A.customerName,B.productName,B.unitPrice, (RANK() OVER
            (ORDER BY A.customerName,B.productName,B.unitPrice DESC)) AS rnk
from tbl_Customer as A
inner join tbl_custProductSales on A.customerId=tbl_custProductSales.customerId inner join
tbl_Product as B on B.productId=tbl_custProductSales.productId
Group by A.customerName,B.productName,B.unitPrice
        ) AS A
        WHERE A.rnk = 2
also refer
http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/
hope this will help you.
Sanjeeb Lenka12y ago
plz provide some sample data so we can able to understand

try something like this

select A.customerName,B.productName,B.unitPrice from tbl_Customer as A
inner join tbl_custProductSales c on A.customerId=c.customerId inner join
tbl_Product as B on B.productId=c.productId
where MAX(B.unitPrice)<(select MAX(unitPrice) from tbl_Product)