Link to home
Start Free TrialLog in
Avatar of lindabalestra
lindabalestraFlag for United States of America

asked on

Find highest bid for each item

I have an auction database with a table that contains the following fields:
AuctItemID
BidAmount
DateTimeofBid
AuctItemName
EmployeeLastName
EmployeeFirstName

I want to query for and return the record with the highest bid amount for each auction item id (there are multiple rows for each ID).  The closest I can get is returning the highest bid and displaying only the fields AuctItemID, MaxOfBidAmount and AuctItemName.  If I include any of the other fields, which I need, I get multiple records for each item id.  Any help would be appreciated!
Avatar of mbizup
mbizup
Flag of Kazakhstan image

To get the max bid, you need to restrict the columns:

SELECT MAX(BidAmount) AS MaxBid, AuctItemID
FROM YourTable
ORDER BY AuctItemID

Open in new window


If you need additional columns you'll have to use that as a subquery:

SELECT t.* FROM 
FROM
YourTable t
INNER JOIN
(SELECT MAX(BidAmount) AS MaxBid, AuctItemID
FROM YourTable
ORDER BY AuctItemID) q
ON t.BidAmount = q.MaxBid and t.AuctItemID = q.AuctItemID

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lindabalestra

ASKER

This is what I used:

SELECT t.*
FROM
tblTotalBids t
INNER JOIN
(SELECT MAX(BidAmount) AS MaxBid, AuctItemID
FROM tblTotalBids
ORDER BY AuctItemID) q
ON t.BidAmount = q.MaxBid and t.AuctItemID = q.AuctItemID

But I am getting this error:
You tried to execute a query that does not include the specified expression 'AuctItemID' as part of an aggregate function
OOPS, sorry mbizup, didn't see your second post.  Works perfectly, thanks so much!!
Awesome - thanks so much
Glad to help out