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 I have a sql query which returns a sum of total amount by user and I am trying to add another column which uses the total and multiply it by a number (23h) Troubleshooting Deadlocks in SQL Server (10d) Last Login date and time (11d) Negative effects of High VLF counts (11d) Need to return a value that indicates that a record has been added, but not when a record is modified (12d) Indexex on low cardinality fields (13d) Error in stored procedure (13d) Spam post flagging (13d)