Sunday, March 11, 2012

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!

ThanksHey,

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.|||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.

No comments:

Post a Comment