如何根据ID将SQL限制为一条记录

Question

I have a table called bids, that have multiple rows.

these all have unique IDs however they have a listingID as well, so everytime it inserts it inserts a new row but with that listingID.

I'm trying to only return one unique result for the ListingID as appose to all the bids in the table , I tried SELECT DISTINCT and group by, but both didn't seem to work.

At the moment this is printing all the records from the table 'bids'

I would like to only print the last record for the listingID column.

$bids = $this->db->query("SELECT bidID,listingID, listing_title, bid_date, username,amount, starting_, sold, vintage, bottles, size, cases, sold_date, bid_type,
FORMAT(`bin`, 0) AS `bin`,
(CASE
WHEN ( SELECT COUNT(*) FROM bids WHERE bid_listing = listingID )
THEN
(SELECT FORMAT(amount,0) FROM bids WHERE bid_listing = listingID  ORDER BY bidID DESC LIMIT 1)
ELSE
FORMAT(`starting_`, 0)
END
) AS `starting`
FROM (`bids`)
JOIN listings ON listingID = bid_listing
JOIN users ON list_uID = userID
WHERE bidder_ID = $userID
ORDER BY bidID DESC");

Answer 1

Try this sql. I used b1.* because I don't know what fields you're trying to return.

$bids = $this->db->query("select b1.* from bids b1
        left join bids b2 on (b1.listingID  = b2.listingID and b1.bidID < b2.bidID
        where b2.bidID is null");