mysql query for this problem

Question

hi i have a table as follows id,school,address,city,state,phone,email,fax . There is almost 40 cities . I need to select 1 entry from each city. can any one guide me

example

city 

 a1     30 entries

 a2     12 entries

......
 a40    36 entries

 Pick 1 from each a1,a40

Answer 1

select * from your_table_name group by state, city

and with a Random select

select * from your_table_name group by state, city order by rand()

Answer 2

select * 
  from table_name 
 where id in (  select MAX(id) 
                  from table_name 
              group by city
             )

Answer 3

in mysql you can simply say:

select * from table group by city

Answer 4

In general I prefer to tackle these cases with a JOIN to a sub query, as in the following example:

SELECT    s.id, s.school, s.address, s.city, s.phone, s.email, s.fax
FROM      schools s
JOIN      (SELECT   MAX(id) as max_id
           FROM     schools
           GROUP BY city) sub_s ON (sub_s.max_id = s.id);

The JOIN is practically restricting your result set to entries with distinct cities. This is also assuming that you want to select the entry with the highest id when you have multiple entries for each city. If you prefer to choose the lowest id, you can use the MIN() aggregate function in the sub query instead of MAX().

Answer 5

SELECT DISTINCT city FROM tablename