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

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 disti开发者_运维问答nction. Any suggestions on solving this issue?

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.

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

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 

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