CODEWITHSUNDEEP
mysqlsql
compcobalt
compcobalt

Reputation: 1362

Select only unique row/record in 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...

Upvotes: 6

Views: 22041

Answers (4)

Bohemian
Bohemian

Reputation: 424983

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

Upvotes: 8

Ray
Ray

Reputation: 41418

You can us a function in a GROUP BY to convert all names to upper or lowercase:

 SELECT  city,state_prefix,lattitude,longitude 
        FROM zip_code WHERE city LIKE '$queryString%'
        GROUP BY UPPER(city), UPPER(state_prefix) LIMIT 10

I'm assuming if a city and state match, lattitude and longitude can be safely rolled up.

Upvotes: 2

O. Jones
O. Jones

Reputation: 108641

It's possible your table, or your city column, has a case-sensitive collation setting.

You can choose the collation explicitly in your SELECT statement like so. If you choose a case-insensitive collation, the DISTINCT keyword will do what you want it to do.

SELECT DISTINCT
        city COLLATE utf8_general_ci,
        state_prefix COLLATE utf8_general_ci,
        lattitude,longitude  
 FROM zip_code 
WHERE city LIKE '$queryString%' 
LIMIT 10

Upvotes: 2

fthiella
fthiella

Reputation: 49049

It doesn't work on other DBMS, but in MySql you could use this:

SELECT city, state_prefix,lattitude,longitude
FROM zip_code WHERE city LIKE '$queryString%'
GROUP BY city
LIMIT 10

MySql ignores case when comparing strings, e.g. this returns true:

select 'hello'='Hello '

and you can also group by a single field (city in this case) and return all others field. Values of other fields are undetermined, they are usually the ones in the first row but you can't relay on that.

Upvotes: 2

Related Questions