Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: select largest COUNT grouped by first letter of field

    I have a table with several columns, some of which are
    NAME, CITY, and STATE.
    There are hundreds of thousands of records.
    I'd like to select the CITY with the largest number of records in each STATE for each letter of the alphabet, if there is a CITY starting with that letter. (not a lot of states have cities that start with Z)

    So restated, I want to select the city that has the most records out of all cities starting with 'A', then 'B', etc.

    thanks for any help you can offer!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without more details, I can only offer general suggestions...

    1. Decide how you want to store the data (a CREATE TABLE would help).
    2. Decide how you want to group the data. Based on your post it seems likely that you want to group by city and state.
    3. Decide the selection criteria. Based on your post, this might be by state and first letter of city name.
    4. Once you decide what you want, then you need to work on how to get it using MySQL.

    After you get to this point, post the CREATE TABLE statement and the two SELECT statements that I've outlined above. That should give us enough insight into your problem to help you finish the solution.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2008
    Posts
    34
    Nevermind. I just wrote 26 seperate queries.
    Last edited by curtmorehouse; 12-28-13 at 12:11.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suspect the subtext is
    make an effort, come back if you are still haveing problems
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Curt,

    You can do this with a couple of small queries:

    Query1:
    Code:
    Select State, SubString(City, 1, 1) As Alpha, Max(Population) As MaxPopulation
    From   YourTable
    Group By State, SubString(City, 1, 1)

    Query2:
    Code:
    Select a.State, a.City, a.Population
    From   YourTable As a Inner Join Query1 As b on
              a.State = b.State And
              Substring(a.City, 1, 1) = b.Alpha And
              a.Population = b.MaxPopulation
    hth,
    Wayne

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •