Results 1 to 2 of 2

Thread: problem with aggregate functions..

  1. #1
    Join Date
    Jan 2003

    Question problem with aggregate functions..

    I really donít know how to solve this...

    How do I get the Chemclass which contains the largest number of antibiotics ?

    relations given:
    Antibiotic (AntibioticID, Name, SubclassID)
    Subclass (SubclassID, Name, ChemclassID)
    probably not needed, but given: Chemclass(ChemclassID, Name)

    Many thanks in advance for any suggestion !

  2. #2
    Join Date
    Dec 2002
    If I'm understanding your request correctly, the chemclass that contains the largest number of antibiotics will have the most rows in the subclass table. In that case:

    select chemclassID from subclass
    group by chemclassID
    having count(chemclassID) =
    select max(amt) from
    select chemclassID, count(chemclassID) as amt
    from subclass
    group by chemclassID) as t2

    Let me know if this helps,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts