Finding the Most Popular Field Values

By Bill Graziano on 8 September 2000 | 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


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

Unpivot (31m)

What variable type could I use in a trigger to read information from a text field (2h)

Missing Range from SQl Table - TSQL (20h)

Insert trigger to run an ASPX script passing record columns as parameters (1d)

Find the closest date with associated column (1d)

SQL Query Help with roll up (2d)

Need help creating trigger to capture single and multiple updates (2d)

Books and Resources (2d)

- Advertisement -