Queries to select MAX

[June7]

My examples do not have GROUP BY in the outer query.

[BobBridges]

Yeah, but I think they have to, June7; your examples were closer than my just-plain-wrong one, but he wants to pick out the bidder that submitted the most bids, and that requires a COUNT function in the outer query.

But despite my defense, I must be doing something wrong if my example didn't work. Guess I'll have to go try it, again, and see whether I can figure out what I did wrong. (Gotta be user error—yeah, that's it....) I'll be back.

[June7]

I read 'most' to mean the largest bid. I will have to visit this again later.

[BobBridges]

I saw that, June7, and since johnseito didn't correct you I thought at first you must be right. Maybe you still are. But I'm looking carefully at his first post, and his first attempt, and now I'm pretty sure you and I were both wrong; he wants the bidder who submitted the most bids.

Anyway, I went back to Access and tried my own code, several different ways, and it's slowly coming to me that all along I've been trying to make "MAX" mean something that it simply doesn't. You're making sense in English, johnseito, but MAX isn't an English word, it just looks like an English word; in Access it's a specific function that doesn't do what I thought we could make it do.

Look at it this way: MAX is an aggregate function. Aggregate functions do something about all the records in a group. Specifically what MAX does is look through the specified field and pick out the one in that group that has the highest value. Now, you want to know which user submitted the most bids, so you're grouping on user ID, and you must get MAX to return the field in that group that has the highest ... what?

"MAX(BidAmount)" (if you have such a field) would make sense. But if you want the maximum count, COUNT(*) isn't one of the fields in the group, it's simply the number of rows. MAX can only pick out the greatest field value. For what you want, June7 is right: You need TOP.

Next question: Why do you want to do it with MAX? If you're just exploring, I like it (I do the same thing) but now I've explored and convinced myself that MAX doesn't work that way. Oh, I think you could get it to do it by doing a third-level SELECT, but that's just going around your elbow to get to your thumb. But if you need it for some reason—if you've decided that TOP isn't working for you after all—then it's probably because I have still misunderstood your question, and we should back up and try again.

[BobBridges]

I'm still pondering this. If June7 was right all along about you wanting the highest bid, even though you spoke of the number of bids, then maybe you meant both. Like this: "I want to pick out the bidder who made the highest bid, and I want to show his user ID, his name, and how many bids he made". Is that right?

If so, we need a) the ID of the highest bidder:

SELECT TOP 1  Bidder FROM BID ORDER BY BidAmt DESC

That picks out the top bid and keeps the corresponding bidder ID. About that bidder you want to show b) the following:

SELECT user.uid, user.uname, count(*) as nBID
FROM user INNER JOIN bid ON bid.bidder = user.uid
WHERE user.userid = (SELECT TOP 1  Bidder FROM BID ORDER BY BidAmt DESC)
GROUP BY uid, uname

How's that? I thought at first it might be "WHERE user.userid in (subselect)", but when I tested that Access went away to think about it and hasn't returned yet. Oh, now it has; it works, it just takes longer. A lot longer, for some reason.

[johnseito]

Ok, thanks. If the Max is not going to work is fine. Just thought I try different way to solve the problem and maybe learn a new method.

I'll think some more and try other option and see what I could come up with. :-)