Return distinct query with some non-distinct columns

[delphi319]

Hello All... I hope this is doable, as it would make my life much easier right now.

I am writing a simple auction site for a charity auction at our company. I have a table called "Bids" that includes the Bid entries for a given Item.

The table includes the following cols:
-bidID (PK)
-itemID (what item we are bidding on)
-userID (what user is bidding)
-bidAmount
-bidTime

My goal is to have a query that returns the highest bidder and ONLY the highest bid for that user. For instance say a user bids 5 times and another user bids 3 times. I want to return a dataset that has essentially two rows: one for each user with the highest bid and time for each user.

SELECT * FROM Bids WHERE Bids.itemID=1
ORDER BY bidAmount DESC, bidTime;

This query returns the highest bidder(s), but includes all of the bids, like follows:

BidId Item BidAmount BidTime Usr
1 1 $1,700.00 9/13/2004 10:33:09 AM 1
2 1 $1,700.00 9/13/2004 10:33:20 AM 2
3 1 $1,600.00 9/13/2004 10:30:53 AM 2
4 1 $1,550.00 9/13/2004 10:33:40 AM 1
5 1 $1,500.00 9/13/2004 10:27:55 AM 3

Ideally, I'd like to return the following:
1 1 $1,700.00 9/13/2004 10:33:09 AM 1
2 1 $1,700.00 9/13/2004 10:33:20 AM 2
5 1 $1,500.00 9/13/2004 10:27:55 AM 3

Notice, that this query has only the highest bid for each particular user, and EVERY user who bids on an item will be in the query, regardless if they win or not.

Is this possible? I'm bashing my head here! I know if I was going for the userID only, that I can get that distinctly, but since I'm adding addition columns it complicates things.

 

[lespaul]

Depending on your Access version, this may work:

SELECT * FROM Bids INNER JOIN (SELECT Usr, Max(BidTime) From Bids) As A ON Bids.Usr = A.Usr AND Bids.BidTime = A.BidTime.

Leslie

 

[delphi319]

Thanks Leslie that seems to do the trick. I modified it a bit to MAX on the BidAmount instead of bidTime, but it seems to work fluently.

One more question... I am developing this on Access, since it is relatively simple and doesn't require account permissions on our servers here. In a few days I am converting this to Oracle. Do you see any problems with this query that may excuse it from working on Oracle? Everything seems to be std SQL, so I don't expect any problems...

Thanks again!

 

[lespaul]

no the query should work fine against Oracle.

Glad to help! Sorry I got the wrong data!

leslie