Select only unique row/record in mysql

Question

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...

Answer 1

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().