"Vinita Sharma" <sharmavi@mail.armstrong.edu> wrote in message
news:OnhH2Oo6DHA.1636@TK2MSFTNGP12.phx.gbl...
> Hi All,
>
> I have the following table suppliers and product.
>
> The Supplier table have two columns Supplier_id and Supplier_name.
> Below is my data in the supplier table:
>
>
> Supplier_Id
> Supplier_Name
>
> 2
> New Orleans Cajun Delights
>
> 3
> Grandma Kelly's homestead
>
> 16
> Bigfoot Breweries
>
> 19
> New England Seafood Cannery
>
> 5
> New Mexico Seafood
>
> 4
> Indian Spices
>
>
>
>
> My Products table have 3 columns Product_id, Product_name and supplier_id
>
> I have the following data in my products table:
>
>
>
> Product_Id
> Product_Name
> Supplier_Id
>
> 4
> Chef Anton's Cajun Seasoning
> 2
>
> 5
> Chef Anton's Gumbo Mix
> 2
>
> 65
> Louisiana Fiery Hot Pepper Sauce
> 2
>
> 66
> Louisiana Hot Spice Okra
> 2
>
> 6
> Grandma's Boysenberry Spread
> 3
>
> 7
> Uncle Bob's Organic Dried Pears
> 3
>
> 8
> Northwood's canaberry sauce
> 3
>
> 34
> Sasquach Ale
> 16
>
> 35
> Steeleye Stout
> 16
>
> 67
> Laughing Lumberjack Lager
> 16
>
> 40
> Boston Crab Meat
> 19
>
> 41
> Jack's New England Clam Chowder
> 19
>
> 75
> Chicago Pizza
> NULL
>
> 22
> Indian Hot Sauce
> NULL
>
>
>
>
> I want to find the supplier_name of the supplier who is supplying maximum
> products.
>
> Can anybody help me with the query?
>
> Thanks,
>
> Vinita
>
>

"Andy Svendsen" <andymcdba1@NOMORESPAM.yahoo.com> wrote in message
news:uin4tno6DHA.2496@TK2MSFTNGP09.phx.gbl...
> Why not got for the top 5-- though you can for the top 1.
>
> SELECT top 5 supplier_name, count(*)
> FROM suppliers,
> product
> WHERE suppliers.supplier_id = product.supplier_id
>
> group by supplier_name
> order by count(*) DESC
>
> --
> *******************************************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@NOMORESPAM.yahoo.com
>
> Please remove NOMORESPAM before replying.
>
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
>
> This posting is provided "as is" with no warranties
> and confers no rights.
>
> *******************************************************************
> "Vinita Sharma" <sharmavi@mail.armstrong.edu> wrote in message
> news:OnhH2Oo6DHA.1636@TK2MSFTNGP12.phx.gbl...
> > Hi All,
> >
> > I have the following table suppliers and product.
> >
> > The Supplier table have two columns Supplier_id and Supplier_name.
> > Below is my data in the supplier table:
> >
> >
> > Supplier_Id
> > Supplier_Name
> >
> > 2
> > New Orleans Cajun Delights
> >
> > 3
> > Grandma Kelly's homestead
> >
> > 16
> > Bigfoot Breweries
> >
> > 19
> > New England Seafood Cannery
> >
> > 5
> > New Mexico Seafood
> >
> > 4
> > Indian Spices
> >
> >
> >
> >
> > My Products table have 3 columns Product_id, Product_name and
supplier_id
> >
> > I have the following data in my products table:
> >
> >
> >
> > Product_Id
> > Product_Name
> > Supplier_Id
> >
> > 4
> > Chef Anton's Cajun Seasoning
> > 2
> >
> > 5
> > Chef Anton's Gumbo Mix
> > 2
> >
> > 65
> > Louisiana Fiery Hot Pepper Sauce
> > 2
> >
> > 66
> > Louisiana Hot Spice Okra
> > 2
> >
> > 6
> > Grandma's Boysenberry Spread
> > 3
> >
> > 7
> > Uncle Bob's Organic Dried Pears
> > 3
> >
> > 8
> > Northwood's canaberry sauce
> > 3
> >
> > 34
> > Sasquach Ale
> > 16
> >
> > 35
> > Steeleye Stout
> > 16
> >
> > 67
> > Laughing Lumberjack Lager
> > 16
> >
> > 40
> > Boston Crab Meat
> > 19
> >
> > 41
> > Jack's New England Clam Chowder
> > 19
> >
> > 75
> > Chicago Pizza
> > NULL
> >
> > 22
> > Indian Hot Sauce
> > NULL
> >
> >
> >
> >
> > I want to find the supplier_name of the supplier who is supplying
maximum
> > products.
> >
> > Can anybody help me with the query?
> >
> > Thanks,
> >
> > Vinita
> >
> >
>
>