How to write this Query?

Discussion in 'MySQL' started by rahman15, Apr 23, 2006.

0
  1. #1
    I have table which have following fields with other fields:
    - State (50 US states)
    - Cities (All US cities0
    - pop_2000 (2000 City population)

    I have total about 25000 records. I want to write a SQL statement which will select Top 10 Cities from each State based on highest population of the cities.

    I tried different Query without any success. Anyone has idea how to do this. I am using mySQL version 4.x.

    Thanks,
     
    rahman15, Apr 23, 2006 IP
  2. kashem

    kashem Banned

    Messages:
    1,250
    Likes Received:
    76
    Best Answers:
    0
    Trophy Points:
    0
    #2
    I never see mySql.

    I use sql server. But the following code should work as well on mysql.

    try and let me know the result


    select state,cities, pop_2000 from t tou where cities
    in (

    select top 10 Cities from t ti where
    ti.state=tou.state order by pop_2000)
     
    kashem, Apr 23, 2006 IP
  3. kashem

    kashem Banned

    Messages:
    1,250
    Likes Received:
    76
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I am sorry . for highest population it should be


    select state,cities, pop_2000 from t tou where cities
    in (

    select top 10 Cities from t ti where
    ti.state=tou.state order by pop_2000 desc)


    You have huge data, it might be slower on your case
     
    kashem, Apr 23, 2006 IP