Code:
SELECT uid, uname, count(*) AS nBID
FROM [user] INNER JOIN bid ON bid.bidder = user.uid
GROUP BY uid, uname
HAVING count(*)=MAX(count(*));
Originally Posted by
June7
Bob, that criteria expression makes no sense.
Originally Posted by
johnseito
BobBridges, This syntax is wrong.
Well, it made sense to me. But I tried it in Access, confirmed that Access wouldn't buy it, and then went to the reference to see what's wrong with it. And now that I see what I was trying for, I can see why it doesn't make sense to Access after all. Here's one that seems to work instead:
Code:
SELECT top uid, uname, count(*) AS nBID
FROM [user] INNER JOIN bid ON bid.bidder = user.uid
GROUP BY uid, uname
order by count(*) desc;
Better?
And yes, June7, it turns out that COUNT(pretty-much-anything) will work. I've only used COUNT(*); I didn't realize it doesn't care what argument I supply.