I am having problem is selecting non high bids. For example user id 5 made 2 bigs on a product id 20 and both are smaller than the highest bid that is posted by a user id 22. Now I want to select that product for whom user id 5 did not made highest bid.
Thanks
since one user can place more than one bid in that case it don't gives correct result.
Like user id 2 placed 3 bids of 500,520 and 580 [if we assume that 580 is the max bid by any user it will consider 520 as smaller than max and show this result]
SELECT
DISTINCT bids.FK_PRODUCT_ID,max(bid_price)
FROM
bids
WHERE
FK_USER_ID = 2
AND
max(bid_price) < (
SELECT
max(lower_bids.bid_price)
FROM
bids AS lower_bids
WHERE
bids.FK_PRODUCT_ID = lower_bids.FK_PRODUCT_ID
)
group by bids.FK_PRODUCT_ID