Finding the Most Popular Field Values

By Bill Graziano on 8 September 2000 | 5 Comments | Tags: SELECT


Kobiashi Maru writes "Say you have a table with all the zipcodes in the United States. The fields are Zipcode, City, State Abreviation. It's pretty easy to find the Cities and States with the most zipcodes. But how would you build a query that would find the City Name that is the most "popular". For example, New York City may have the most zip codes, but not very many states have a NYC. Lots of states have a "Springfield". So which city name is most popular among the US?" Ok, I'll make you a deal. I'll provide the query but you have to report back with the answer.

I'm curious what the most popular city in the U.S. is. This is actually a fairly straight forward query. If we run it against the pubs database using the authors table it looks something like this:

SELECT TOP 5 city, Number=count(*)
FROM authors
GROUP BY city
ORDER BY Number desc


The GROUP BY is used to return a count for each value. We are sorting by that count in descending order and only returning the top 5. In pubs Oakland is the winner with 5 authors. Now tell us which city is the most popular in the U.S. And I like the handle you posted under. Clever Star Trek reference?

-graz

Discuss this article: 5 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Joining to the Next Sequential Row (2 April 2008)

Writing Outer Joins in T-SQL (11 February 2008)

How to Use GROUP BY with Distinct Aggregates and Derived tables (31 July 2007)

How to Use GROUP BY in SQL Server (30 July 2007)

SQL Server 2005: Using OVER() with Aggregate Functions (21 May 2007)

Server Side Paging using SQL Server 2005 (4 January 2007)

Using XQuery, New Large DataTypes, and More (9 May 2006)

Counting Parents and Children with Count Distinct (10 January 2006)

Other Recent Forum Posts

Analyze existing SQL DB status of production (1 Reply)

Traveling Salesman Problem Algorithm (3 Replies)

Partitioning existing Databas (0 Replies)

Problem when importing from Excel (0 Replies)

Dates from weeknumber (1 Reply)

Query Access table (2 Replies)

Update Date based on Employee (0 Replies)

Pivot Table (0 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -