Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419

    Queries to select MAX


    Hi,

    I have this sql statement, what is doing is select the user id and user name, and also count the number of bid the user makes.
    But I only want it to show the user who bid the most. How do I adjust this query to make it happen? Thanks!!!

    Code:
    SELECT uid, uname, count(bidder) as nBID
    FROM user INNER JOIN bid ON bid.bidder = user.uid
    GROUP BY uid, uname

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,423
    Try the Top n parameter.

    SELECT TOP 1 uid, uname, count(bidder) As nBID FROM user INNER JOIN bid ON bid.bidder = user.uid GROUP BY uid, uname ORDER BY Count(bidder) DESC;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Thank you. This works, but do you know how I could query out the same info with the Max function?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,423
    Try:

    1. SELECT * FROM user WHERE bid IN (SELECT Max([bid]) FROM user);

    2. SELECT * FROM user WHERE bid = (SELECT Max([bid]) FROM user);

    3. SELECT * FROM user WHERE bid = DMax("bid","user");
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    I am using GROUP BY, so I can't use WHERE but I have to use HAVING right?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,423
    Probably. Build in query designer. It will use HAVING if necessary.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    what is build in query designer?

    Code:
    SELECT uid, uname, count(bidder) AS nBID
    FROM [user] INNER JOIN bid ON bid.bidder = user.uid
    GROUP BY uid, uname
    HAVING MAX(count(bidder));
    I don't know what is wrong with this HAVING clause. Is not working because it cannot aggregate function in the expression. But if I put MAX(bidder) = 5.
    It will work.

  8. #8
    BobBridges is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Location
    USA
    Posts
    22
    "COUNT(BIDDER)"? I don't think I've ever used that; don't you want "COUNT(*)"? If you're looking for the person who submitted the most bids, I'd expect the code to read
    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(*));
    But I'm guessing; you didn't say how it isn't working. What does it do instead of "work"?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,423
    Count() should work with either the wildcard or a field reference. After all, it's counting records, not whether the field has a value. And Bob, that criteria expression makes no sense.

    You aren't using the nested query syntax shown in my examples.

    Where are you constructing this SQL statement?

    If not already, you should get familiar with the Access query builder Design View - a very basic Access tool. Access Help has guidelines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Quote Originally Posted by BobBridges View Post


    Code:
      HAVING count(*)=MAX(count(*));
    ?


    BobBridges, This syntax is wrong.

  11. #11
    BobBridges is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Location
    USA
    Posts
    22
    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(*));
    Quote Originally Posted by June7
    Bob, that criteria expression makes no sense.
    Quote 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.

  12. #12
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    BobBridges,

    We are going back in circle, June7 provided a similar code to the one you just provided and is a great code that works great.
    But I was wondering if it can be done another way using the MAX function.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,423
    I already showed you the methods in post 4. Needs subquery.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    BobBridges is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Location
    USA
    Posts
    22
    I think he's right, johnseito. I thought I could get around the subquery (not that a subquery is a disadvantage) by using MAX(...) in the HAVING clause; but Access rejected that, and after some thought I understood why. It didn't make sense, as both you and June7 pointed out. It needs a subquery.

    If I understand your table's structure, it won't be exactly the subquery he suggested, though. He was describing generally, but you need COUNT(*) just to add up the number of bids for each bidder, right? So your subquery has to be SELECT COUNT(*) FROM USER GROUP BY UID. That counts up the number of bids for each UID. Then your main query will use it as you planned:
    Code:
    SELECT uid, uname, count(*) as nBID
    FROM user INNER JOIN bid ON bid.bidder = user.uid
    GROUP BY uid, uname
    HAVING count(*)=MAX(SELECT COUNT(*) FROM BID GROUP BY BIDDER)
    Does that work? I haven't tested it, but it seems to me that it should.

  15. #15
    johnseito is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    419
    Hi BobBridges,

    Thanks for your effort in providing the sub-query with the Max function in it. However I tried your code and there is an error.
    You can't have a MAX function outside of the SELECT statement or the sub-query, I am guessing.

    Error message, can't have aggregate function in expression (MAX()=).


    This is my version of the code, I got the same error message:

    Code:
    SELECT user.uid, user.uname, count(*) as nBID
    FROM user INNER JOIN bid ON bid.bidder = user.uid
    GROUP BY uid, uname
    HAVING count(*) = (SELECT MAX (count(*)) FROM user INNER JOIN bid on bid.bidder = user.uid);

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Queries based on a Select Case
    By dccjr in forum Queries
    Replies: 3
    Last Post: 03-05-2013, 04:12 PM
  2. Replies: 1
    Last Post: 10-08-2012, 09:01 AM
  3. Replies: 5
    Last Post: 07-26-2012, 12:59 PM
  4. Replies: 8
    Last Post: 04-21-2011, 05:29 PM
  5. Replies: 1
    Last Post: 10-22-2010, 10:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums