Problem with SQL query

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

quote:

---

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

---

|||Thanks a zillion.
It worked
"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...
supplier_id
maximum
>