Question 190756 in Mysql Question posted in
The official MySQL documentation can be found here.

Find out position from all bidders of an auction taking only latest submit of the vendor using mysql

We have a table bids(id,auction_id, vendor_id,bid_price,created_at) where one vendor will have multiple bids but we take the latest using the created_at column

We need to find the position of the vendor from the bids of all vendors where his position is determined by the lowest bid from the same auction_id using MySQL .assumeing vendor id = 5 and auction id = 7

I have written the following query

SET @vendor_id := 5;
SET @auction_id := 7;

SELECT
    vendor_id,
    lowest_bid,
    FIND_IN_SET(vendor_id, sorted_vendors) AS position
FROM (
    SELECT
        b1.vendor_id,
        b1.bid_price AS lowest_bid,
        (
            SELECT GROUP_CONCAT(DISTINCT vendor_id ORDER BY bid_price)
            FROM bids b2
            WHERE b2.auction_id = @auction_id
        ) AS sorted_vendors
    FROM bids b1
    INNER JOIN (
        SELECT vendor_id, MAX(created_at) AS latest_created_at
        FROM bids
        WHERE auction_id = @auction_id
        GROUP BY vendor_id
    ) latest_bids ON b1.vendor_id = latest_bids.vendor_id AND b1.created_at = latest_bids.latest_created_at
    WHERE b1.auction_id = @auction_id
) AS subquery
WHERE vendor_id = @vendor_id
ORDER BY lowest_bid
LIMIT 1;

But it is showing the wrong position of the vendor. Here is the DBFiddle

https://www.db-fiddle.com/f/prDKJgo5QPgwGj98544mCX/0

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I could solve it using the following query

    SET @vendor_id := 5;
SET @auction_id := 7;

SELECT position
FROM (
    SELECT
        vendor_id,
        ROW_NUMBER() OVER (ORDER BY lowest_bid) AS position
    FROM (
        SELECT
            vendor_id,
            MIN(bid_price) AS lowest_bid
        FROM (
            SELECT
                vendor_id,
                bid_price,
                ROW_NUMBER() OVER (PARTITION BY vendor_id ORDER BY created_at DESC) AS rn
            FROM bids
            WHERE auction_id = @auction_id
        ) AS latest_bids
        WHERE rn = 1
        GROUP BY vendor_id
    ) AS vendor_bids
) AS positions
WHERE vendor_id = @vendor_id;

    In this query, the innermost subquery latest_bids selects the latest bids from each vendor by assigning row numbers based on the descending order of created_at for each vendor. The middle subquery vendor_bids calculates the lowest bid (MIN(bid_price)) among the latest bids for each vendor and assigns positions using the ROW_NUMBER() function. Finally, the outer query filters the result to include only the position of vendor 5.


  2. 0

    this should give you the dataset you want which you can then filter as you need. It has bid_postn = 1 for the highest bid per auction but you can reverse this to be the lowest by changing the partition clause to use asc not desc.

    select *
from (
    select b1.id, b1.auction_id,  b1.vendor_id,  b1.no_of_vehicle,  b1.bid_price,  b1.bid_date,   b1.active,  b1.created_by,  b1.updated_by,  b1.created_at,  b1.updated_at, row_number() over (partition by b1.auction_id order by b1.bid_price desc) bid_postn
    from bids b1
    inner join (select vendor_id, auction_id, max(created_at) created_at
                from bids
                group by vendor_id, auction_id) b2 
    on b1.vendor_id = b2.vendor_id and b1.auction_id = b2.auction_id and b1.created_at = b2.created_at

) as t1
order by auction_id, bid_postn
;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search