Query manipulation help

Transact-SQL
1

Hello all,

Below is the result from my query:

SELECT Order_Date, Purchased_Date, Buyer
FROM SalesTBL

Order_Date Purchased_Date Buyer
01/05/2016 03/21/2016 A
01/05/2016 12/30/2016 B

03/06/2016 04/07/2016 C
03/06/2016 04/08/2016 A

02/11/2016 02/13/2016 C
02/11/2016 02/21/2016 B

05/04/2016 05/07/2016 A
05/04/2016 05/04/2016 A

What I am trying to achieve is to get above result set as below:

Order_Date Purchased_Date Buyer
01/05/2016 03/21/2016 A

03/06/2016 04/08/2016 A

02/11/2016 02/21/2016 B

05/04/2016 05/04/2016 A

So, basically the order of the buyer I need to seeks first is A. If A is not available then seeks buyer B, if B is not available then seeks buyer C. If there are two same buyers, then I want to pick the one that closest to the Order_Date, like the last example, I only want to display:
05/04/2016 05/04/2016 A
because 05/04/2016 is the closest date to the order_date.

Anyone can help me to update my basic above query?

Thanks all

2 
select *
from
(
    select *, rn = row_number() over (partition by order_date 
                                          order by buyer, purchase_date)
    from yourtable
) d
where rn = 1
1 Like
3

Thank you Khtan