0

for this query:

  SELECT city_alias,population
    FROM `table_name`
   where state='NV' and city_type='P'
order by `population` DESC
   LIMIT 0, 5              


Las Vegas   70994
Las Vegas   70123
Las Vegas   64096
North Las Vegas 60589
Las Vegas   58794
Las Vegas   56300
Las Vegas   55416
North Las Vegas 53928
Las Vegas   51252
Las Vegas   50519
Las Vegas   49778
Las Vegas   49445
Henderson   47214
Henderson   47095
Las Vegas   46055
Las Vegas   45720
Reno            43566
Las Vegas   43072
North Las Vegas 40297
Las Vegas   39909

so i should get as

Las Vegas   70994
North Las Vegas 60589
Henderson   47214
Reno            43566
user1249486
  • 1
  • 1
  • 9
    So many hundreds of duplicates on Stack Overflow, all nicely listed in the "related" column... please always search first. Thanks! – Pekka Mar 05 '12 at 09:39

3 Answers3

1

I hope it would work fine for you

Select from table_name Group by name Having Max(pop);
Mian Zeshan Farooqi
  • 311
  • 1
  • 4
0

You can use this query

Select name from table_Name group by name

If you want to get id and popup use like this

Select name,id,popup from table_Name group by id,popup,name
Mulesoft Developer
  • 2,664
  • 5
  • 28
  • 41
  • thanks for reply but still i didn't got what i actually want.this is the query which i m using "SELECT city FROM `table_name` where state='AL' and city_type='P' order by `population` DESC" so i need result should what i mentioned before.now i am getting duplicate records.if i use "distinct" it'll eliminate all duplicate records so it wont display those records even though it has more population – user1249486 Mar 05 '12 at 09:57
  • when you used where clause it will return you only those column which are congaing state Al and P. you need to use group by clause rather then where clause if still you want to filter some data in group by clause you can use having clause which act same as where clause but where clause is not supported with group by aggregate function – Mulesoft Developer Mar 05 '12 at 10:01
  • i've edited clearly mentioned how is my current query and its result and what i m expecting to get in above question now .so plz – user1249486 Mar 05 '12 at 10:13
  • I have already provided you the solution which you want you just need to implement it in you solution please read group by and having clause in sql it will help you alot – Mulesoft Developer Mar 05 '12 at 10:15
0

Use DISTINCT in this case...

SELECT DISTINCT(city_alias), population FROM table_name where state='NV' and city_type='P' order by population DESC LIMIT 0, 5

Fahim Parkar
  • 30,974
  • 45
  • 160
  • 276