Query Problem

Discussion in 'Databases' started by dizyn, Apr 13, 2009.

0
  1. #1
    I want to select highest bid and highest bidder for a product.

    This query gives me the highest bid but don't gives me correct bidder, Its gives my id of the first bidder.

    I want it to give me highest bid + correct highest bidder that is u.login_id.

    select max(b.bid_price), u.login_id from user u, bids b where b.FK_PRODUCT_ID = 18 and u.PK_ID = b.FK_USER_ID and b.owner = 0 group by b.FK_PRODUCT_ID
    Code (markup):
    any help?

    Thanks.
     
    dizyn, Apr 13, 2009 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    I would try something like this.

    SELECT b.bid_price, u.login_id
    FROM user AS u LEFT JOIN bids AS b
    ON u.PK_ID = b.FK_USER_ID
    WHERE b.FK_PRODUCT_ID = 18 AND b.owner = 0
    GROUP BY b.FK_PRODUCT_ID
    ORDER BY b.bid_price DESC LIMIT 1;

    Also, if you're only needing a single result, the group by is unnecessary.
     
    jestep, Apr 14, 2009 IP