CODEWITHSUNDEEP
sqlsql-serverranking-functions
AbeeCrombie
AbeeCrombie

Reputation: 607

Find Salesperson's second highest sale SQL

I have 3 obvious tables, salesperson, orders and customers. In the order table I have a list of all the orders and the SalesPersonID.

I want to find each salesperson with more than 2 orders and find the order value for the second highest order. I can complete this query if I am asked to find the MAX order value but I can't seem to figure out an easy way to do it for the second highest value.

Newbie here, so please go easy. Thanks in advance.

I've racked my brains reading about ROW_Number and Rank for the past 3 hours but cant figure it out. And im sure there is an easier way than writing my own custom function

Select distinct 
    Orders.SalesID,  MAX(Orders.OrderAmount), SalesPerson.SalesName   
From 
    Orders, SalesPerson
Where 
    Orders.SalesID = SalesPerson.SalesID
Group by 
    Orders.SalesID, SalesPerson.SalesName
Order by 
    MAX(Orders.OrderAmount) DESC

Upvotes: 0

Views: 3975

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use rank function. For each sales person, a rank is assigned based on the order amount. The highest amount is assigned 1. And so on in the descending order.

select salesname, orderamount
from (
select
rank() over(partition by s.SalesName order by o.OrderAmount desc) as rnk 
,s.SalesName  
,o.OrderAmount
From Orders o
join SalesPerson s on o.SalesID = s.SalesID) t
where rnk = 2

Upvotes: 2

Related Questions