Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shakman32
Starting Member
1 Post |
 Posted - 2003-02-25 : 14:32:57
|
| Hello,I have 2 tables. The first is a customer table containing customer ids(primary key) and the cell phone service provider for each customer(foreign key). The second table represents cell phone service providers containing name (primary key). I need to write a query which retrieves the service provider with the most customers. Any ideas on how to do this would be greatly appreciated. Thanx |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2003-02-25 : 14:42:53
|
| There are several ways. I prefer TOP 1 myself. Something along the lines of:SELECT TOP 1 WITH TIES COUNT(SP.<column>), <other columns from SP>FROM <db>.<owner>.<service provider table> AS SP INNER JOIN <db>.<owner>.<customer table> AS C ON SP.<fill in join> = C.<fill in join>GROUP BY <same columns in select clause>ORDER BY COUNT(SP.<same count as column list>) DESC |
 |
|
|
|
|
|
query question