Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Question Unanswered: Complicated query

    I have a table called PizzaToppings. The columns are Topping ID & PizzaID.

    ToppingID is a foreign key from Toppings table & PizzaID is a foreign key from Pizza table.

    Together they form the composite primary key for PizzaToppigs table.

    I need to find out what the most popular topping is.

    ie I have to find the ToppingID with the maximum number of occurrences in the PizzaTopping table.

    PLEASE HELP!

    Thanks

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hey,

    Given the relation

    A B
    ---------- ----------
    a b1
    a b2
    a b3
    a b4
    b b2

    select a, count(*)
    from help
    group by a
    having count(*) >= all (select count(*) from help group by a);

    returns

    A COUNT(*)
    ---------- ----------
    a 4

    I removed the example with 'rownum = 1', as it contained a vital flaw being it only returns row 1, thus if 2 toppings are equal in popularity then only the first one alphabetically would be returned.
    Last edited by r123456; 12-04-03 at 02:52.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Dec 2003
    Posts
    2

    Thumbs up Thanks!

    Thanks MUCH! Very obliged.

    Originally posted by r123456
    Hey,

    Given the relation

    A B
    ---------- ----------
    a b1
    a b2
    a b3
    a b4
    b b2

    select a, count(*)
    from help
    group by a
    having count(*) >= all (select count(*) from help group by a);

    returns

    A COUNT(*)
    ---------- ----------
    a 4

    I removed the example with 'rownum = 1', as it contained a vital flaw being it only returns row 1, thus if 2 toppings are equal in popularity then only the first one alphabetically would be returned.

Posting Permissions

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