Hi 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 Here is my db table structure: CREATE TABLE `bids` ( `PK_ID` int(11) NOT NULL auto_increment, `FK_USER_ID` int(11) default NULL, `FK_PRODUCT_ID` int(11) default NULL, `bid_price` float(11,0) default NULL, `created_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`PK_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ; Code (markup):
If I understood right: SELECT * FROM bids WHERE bid_price NOT IN(SELECT MAX(bid_price) FROM bids GROUP BY FK_USER_ID,FK_PRODUCT_ID); Code (markup): Regards. Edit: Actually the above query will return and user id 20 if it has 2bids (max and any else). This can be fixed the same way checking userid not in sub-query. But, this is not my point. In my opinion your db model is not 100% clear (in particular table bids).Why user can bid more than once on the same product?
Tried this but can't says wots the problem with it. 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 Code (markup):
SELECT bids.FK_PRODUCT_ID,max(bid_price) FROM bids WHERE FK_USER_ID = 2 group by bids.FK_PRODUCT_ID HAVING max(bid_price) < ( SELECT max(lower_bids.bid_price) FROM bids AS lower_bids WHERE bids.FK_PRODUCT_ID = lower_bids.FK_PRODUCT_ID ) Code (markup): Try this Not tested, must works. Edit: Tested and works fine