mysql 多行唯一,在MySQL中仅选择唯一的行/记录

I would like to see how I can get only unique row/records that have a unique city and not care if it's capital so for example:

Akron

akron

aKRON

would only return one record.

I tired something like this but it doesn't work

"SELECT DISTINCT(city) AS city,state_prefix,lattitude,longitude FROM zip_code WHERE city LIKE '$queryString%' LIMIT 10"

Thank you...

解决方案

You can use this mysql-only trick:

SELECT city, state_prefix, lattitude,longitude

FROM zip_code WHERE city LIKE '$queryString%'

GROUP BY city, state_prefix -- Here's the trick

LIMIT 10

This will return the first row encountered for each unique value of city and state_prefix.

Other databases will complain that you have non=-aggregated columns not listed in the group by or some such message.

Edited

Previously I claimed that not using the upper() function on the grouped-by columns it would return all case variations, but that was incorrect - thanks to SalmanA for pointing that out. I verified using SQLFiddle and you don't need to use upper().