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

T-sql - we created Message from app1 and trying to disable from app2 (9h)

SQL select Top 10 records for unique combination of two columns (23h)

SSRS Report Sorting with Grouping Issue (2d)

ORA-01476: divisor is equal to zero (2d)

Create new columns based on min and max values of a record with multiple rows (2d)

Memory Required for Reporting Services 2022 (2d)

Backup sql server large db on cloud (2d)

Selecting x columns but only displaying y columns (2d)

- Advertisement -