How can i select the highest bid for every card?-CodePudding

What we are trying to achieve is to retrieve the highest bid for every card from an auction. Users do not know who has bid how much thus allowing them to bid less or the same amount as other users. The issue we have right now is that we cannot find the right use of GROUP BY. For now we have the following queries:

-- This one does not order the records correctly
SELECT
    DISTINCT ON ("auctionCardId") "auctionCardId", 
    auction_card_bid."userId",
    auction_card_bid.id AS bid_id,
    MAX(auction_card_bid.time),
    MAX(auction_card_bid."bid")
FROM
    auction_card_bid auction_card_bid
    INNER JOIN auction_card ac ON ac.id = auction_card_bid."auctionCardId"
GROUP BY
    auction_card_bid.id,
    auction_card_bid."auctionCardId";

-- Orders the records correctly but it essentially returns all the records
SELECT
    "auctionCardId",
    auction_card_bid."userId",
    auction_card_bid.id AS bid_id,
    MAX(auction_card_bid.time),
    MAX(auction_card_bid."bid")
FROM
    auction_card_bid auction_card_bid
    INNER JOIN auction_card ac on ac.id = auction_card_bid."auctionCardId"
GROUP BY
    auction_card_bid.id,
    auction_card_bid."auctionCardId"
ORDER BY
    auction_card_bid.bid DESC,
    auction_card_bid.time ASC;

We have the following database structure:

auction

id	start	end
1	2020-05-05	2020-05-06

auction_card

id	card_id	auction_id	min_bid
1	1	1	40
2	2	1	50

auction_card_bid

id	card_id	user_id	bid	time
1	1	1	10	2020-05-05 10:00
2	1	2	20	2020-05-05 10:05
3	1	3	20	2020-05-05 10:08
4	2	2	20	2020-05-05 10:10
5	2	3	40	2020-05-05 10:15

The ideal result would be something among the lines of:

user_id	auction_card_id	bid_id	bid	time
2	1	2	20	2020-05-05 10:05
3	2	5	40	2020-05-05 10:15

CodePudding user response：

After a lot of trial and error, we finally found out how to achieve what we wanted.

SELECT DISTINCT ON ("auctionCardId") "auctionCardId" AS  auction_card_id,
                                     auction_card_bid.id bid_id,
                                     value,
                                     time,
                                     "userId",
                                     auction_card."auctionId"
FROM auction_card_bid auction_card_bid
         JOIN auction_card auction_card ON auction_card_bid."auctionCardId" = auction_card.id
WHERE auction_card."auctionId" = 2
ORDER BY "auctionCardId", value DESC, time ASC;