Results 1 to 2 of 2

Thread: problem with aggregate functions..

  1. #1
    Join Date
    Jan 2003
    Posts
    3

    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
    Posts
    181
    Niko,
    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,

    Jeff

Posting Permissions

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