[thelist] Quick SQL Help Please

[Joshua Olson]

> -----Original Message-----
> From: Rob Smith
> Sent: Thursday, October 28, 2004 9:50 AM
> 
> Hi,
> 
> Assume this table:
> 
> Bid 		ID		By
> 3		2		John
> 52		1		Jane
> 3		3		Jim
> 15		1		Josh
> 6		3		James
> 2		2		Jimmy
> 45		1		Joan
> 2		3		Jack
> 
> How do I get the max bids per item showing the correct winning bidder?
> 
> The correct Results should be:
> 
> 52		1		Jane
> 3		2		John
> 6		3		James
> 
> I thought it would be something like:
> 
> SELECT MAX(LatestBid) AS LatestBid, TBayBidID, MAX(Bidder) AS 
> Bidder FROM TBayBid GROUP BY TBayBidID Order by TBayBidID

Rob,

I'm going to assume that Bid = LatestBid and ID = TBayBidID

SELECT drvtable.LatestBid
  , drvtable.TBayBidID
  , TBayBidID.By
  , [other fields from other joined tables, perhaps?]
(
  SELECT Max(LatestBid) AS LatestBid
    , TBayBidID
  FROM TBayBid
  GROUP BY TBayBidID
) drvtable
INNER JOIN TBayBidID
ON drvtable.LatestBid = TBayBidID.LatestBid
[join on other tables if you need other auction/bidder info]
ORDER BY TBayBidID

Something like that?

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168