I want to find closest seller to me by price, only for the ASINs where I am the winner (yes).
There are 1000s of different ASINs, only one winner/asin.
A seller can be a winner in many ASINs.
There is a table: price_detail
|--------------------------------------------------|
| ASIN | Seller_ID | Price | winner |
|--------------------------------------------------|
| B07K7LDKWG | Me | 12.50 | yes |
| B07K7LDKWG | Other Seller1 | 12.60 | no |
| B07K7LDKWG | Other Seller2 | 13.50 | no |
| |
| B07K7M3W5P | Other Seller3 | 10.99 | no |
| B07K7M3W5P | Other Seller1 | 10.99 | yes |
| B07K7M3W5P | Other Seller4 | 11.94 | no |
| B07K7M3W5P | Other Seller2 | 12.50 | no |
|__________________________________________________|
The result I want as under:
|--------------------------------------------------|
| ASIN | Seller_ID | Price | winner |
|--------------------------------------------------|
| B07K7LDKWG | Other Seller1 | 12.60 | no |
|__________________________________________________|
What I have tried so far is as under:
SELECT
pd.asin,
pd.seller_id,
pd.price,
pd.winner
FROM `prices_detail` pd
left join (
select asin,
seller_id,
winner
from price_detail
where winner='yes'
and
seller_id='AVVKLE3L21ZDG'
) v on v.asin=pd.asin
and v.seller_id=pd.seller_id
where (v.asin is null) and (pd.winner <> '1')
group by pd.asin
order by pd.asin, pd.price asc
But this does not return desired result.
Any help is appreciated