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

Subtract total counts from previous row (55m)

Replica Node availability Node (5h)

Is there a better way to do this (padding Time field with zeros) (15h)

Frustrated SQL newbie (18h)

Impact of changing server name SQL Server (19h)

SSRS - hide rows based on the user who run the report (1d)

Incorrect syntax error and I can't tell why (3d)

IN vs EXISTS (3d)

- Advertisement -