query problem

I am having a problem with query, I want to select max bid price, primary key, and FK_PRODUCT_ID against a user id. but i want to select row if and only if the user put max bid in other words i want to select max bid, product id, pkid against a user id if and only if he/she has given max bid on that product.

Here is db 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):

 

One way to do it would be:

SELECT Bids.PK_ID, Bids.FK_USER_ID, bids.FK_Product_ID, Bids.Bid_Price
FROM Bids Inner Join 
    (SELECT bids.FK_PRODUCT_ID, Max(bids.bid_price) AS MaxOfbid_price
     FROM bids
     GROUP BY bids.FK_PRODUCT_ID) AS MaxBids 
ON Bids.FK_Product_ID = Maxbids.FK_Product_ID AND Bids.bid_Price = maxbids.Maxofbid_price;

Code (SQL):

With a "where" clause at the end if you only want it for a specific user.

It is not going to be a very efficient search as you have to have the subquery looking at the complete table and so you would really want to consider archiving bids out of the table regularly or change the method to hold only the max bid against the item so it is only the item you need to query

 

thank you for the reply, i have a question, is there any way i can do this fast by using procedures?

many thanks for the reply.

 

You can stick it into a stored procedure but as a single query there isnt going to be much efficiency gain by it - though best practice is to use stored procedures rather than inline queries.

 

one last question,
I wanted to find the person who bid once on a product and current highest bid no matter who did this bid, I need current highest bid, total number of bids, and only those products where userid 2 has bid once.

my current query:

select bids.PK_ID, bids.FK_USER_ID, bids.FK_PRODUCT_ID, bids.bid_price
from bids inner JOIN
(
	select bids.FK_PRODUCT_ID, max(bids.bid_price) as MaxOfBidPrice
    from bids
    group by bids.FK_PRODUCT_ID
)as MaxBids
on bids.FK_PRODUCT_ID =  MaxBids.FK_PRODUCT_ID and
bids.bid_price =   MaxBids.MaxOfBidPrice

Code (markup):

is giving me highest bid, number of bids