Find 1 record (closest) to me by price - in mysql

Viewed 30
  1. I want to find closest seller to me by price, only for the ASINs where I am the winner (yes).

  2. There are 1000s of different ASINs, only one winner/asin.

  3. 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

0 Answers
Related