SQL Query to find second highest value

Question

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.customerNameB.productNameB.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 -----------------------------------------------------

Jignesh Trivedi · Answer

hi Try... select A.customerNameB.productNameB.unitPrice (RANK() OVER (ORDER BY A.customerNameB.productNameB.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.customerNameB.productNameB.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 Lenka · Answer

plz provide some sample data so we can able to understand try something like this select A.customerNameB.productNameB.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)