Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Unanswered: Select highest bid for each book

    This is the last query I am struggling with in my project. I am using MS Access 2007. I am a newbie and thus will try my best to explain my problem.

    My system is like eBay but only sells books. Customers register, place order for a book, choose whether to directly buy or place bid, are then taken to submit bid if they chose to bid. Internally as soon as they placed their bids, a database populates all the bids for books.

    The query I am struggling with intends to do this - selecting highest bid for each book and display three columns - 1) Customer, 2) Book, 3) Bid amount (highest) so we know the winner.

    I have two tables - 1) customerordersbook - contains customerID, bookID, and a flag that decides if it is a bid or a direct buy (obviously we are talking about when the flag is set. and 2) bid - that has the bidamount, bidtime, etc.

    Here is the query -

    ****************************

    SELECT CustomerOrdersBook.cobCustomerID, CustomerOrdersBook.cobBookID, Bid.bidAmount

    FROM CustomerOrdersBook LEFT JOIN Bid ON CustomerOrdersBook.cobID=Bid.bidCobOrderID /* join is made on the two entries I kept common in two tables */

    WHERE ((CustomerOrdersBook.cobIsThisABid)=True) /* only chooses the bid checked flag valueds */

    AND Bid.bidAmount = (SELECT MAX(Bid.bidAmount) FROM Bid);


    **************************

    The query works, but only displays the HIGHEST amount in the bidAmount column. If I insert GROUP BY statement, the best I have gotten so far is two columns - bookID, and highest price. But then the customerID is left out

    *************

    SELECT CustomerOrdersBook.cobCustomerID, CustomerOrdersBook.cobBookID, Bid.bidAmount

    FROM CustomerOrdersBook LEFT JOIN Bid ON CustomerOrdersBook.cobID=Bid.CBcobOrderID

    WHERE ((CustomerOrdersBook.cobIsThisABid)=True)

    AND Bid.CBBidAmount = (SELECT MAX(Bid.CBBidAmount) FROM Bid)

    GROUP BY Bid.bidID, CustomerOrdersBook.cobCustomerID, CustomerOrdersBook.cobBookID, Bid.bidAmount;

    **************

    The query above, as soon as I put in customerID there goes back to showing all the entries for all bids and not the highest one per book.

    I can see the problem is in MAX, because I need to display the distinct bookIDs, with customers buying those books with highest bids.

    Please help!
    Last edited by pinaklonkar; 04-22-09 at 16:40.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try with a SELECT TOP 1 .... ORDER BY CBBidAmount DESC

    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •