SQL Query - group by more than one column, but distinct

Question

I have a bidding table, as follows:

SellID INT FOREIGN KEY REFERENCES SellItem(SellID),
CusID INT FOREIGN KEY REFERENCES Customer(CusID),
Amount FLOAT NOT NULL,
BidTime DATETIME DEFAULT getdate()

Now in my website I need to show the user the current bids; only the highest bid but without repeating the same user.

  SELECT CusID, 
         Max(Amount) 
    FROM Bid 
   WHERE SellID = 10 
GROUP BY CusID 
ORDER BY Max(Amount) DESC

This is the best I have achieved so far. This gives the CusID of each user with the maximum bid and it is ordered ascending. But I need to get the BidTime for each result as well. When I try to put the BidTime in to the query:

  SELECT CusID, 
         Max(Amount), 
         BidTime 
    FROM Bid 
   WHERE SellID = 10 
GROUP BY CusID 
ORDER BY Max(Amount) DESC 

I am told that "Column 'Bid.BidTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Thus I tried:

  SELECT CusID, Max(Amount), BidTime 
    FROM Bid 
   WHERE SellID = 10 
GROUP BY CusID, BidTime 
ORDER BY Max(Amount) DESC

But this returns all the rows. No distinction. Any suggestions on solving this issue?

Answer 1

One way, assuming another way of thinking about it is to return the LATEST bid by each user:

SELECT b.cusID, b2.LatestBidTime, b.Amount 
FROM Bid b
    JOIN (
        SELECT cusID, MAX(BidTime) AS LatestBidTime
        FROM Bid
        WHERE SellID = 10
        GROUP BY cusID) b2 ON b.cusID = b2.cusID AND b.BidTime = b2.LatestBidTime
WHERE b.SellID = 10

Answer 2

Presumably the highest bid was made most recently, right? So just put MAX on BidTime too.

SELECT CusID, Max(Amount), MAX(BidTime)
FROM Bid 
WHERE SellID=10 
GROUP BY CusID ORDER BY Max(Amount) DESC 

Answer 3

select b.*
from Bid b
inner join (
    SELECT CusID, Max(Amount) as MaxBid
    FROM Bid 
    WHERE SellID=10 
    GROUP BY CusID 
) bm on b.CusID = bm.CusID and b.Amount = bm.MaxBid
where b.SellID = 10
ORDER BY b.Amount DESC 

Answer 4

Use:

  SELECT b.cusid,
         b.amount,
         b.bidtime
    FROM BID b
    JOIN (SELECT t.cusid,
                 t.sellid,
                 MAX(t.amount) AS max_amount
            FROM BID t
           WHERE t.sellid = 10
        GROUP BY t.cusid, t.sellid) x ON x.max_amount = b.amount
                                     AND x.cusid = b.cusid
                                     AND x.sellid = b.sellid
ORDER BY b.amount DESC

Using aggregates means you can not get information at that given value in the same query. You have to associate a copy of the table with the aggregated data (joins are the typical choice) to get the related information.