SQL Query - group by more than one column, but distinct
- by Ranhiru
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?
