How to ignore a column for select distinct in postgresql?

Question

Hello everybody!

I have a SQL (see above) and i would like to know how i can make sure that i don't get doubles concerning the name only. if a name appears in the first Select it's the master and should be ignored in the following selects.

"SELECT name, id, 'place' AS tablename FROM places WHERE lower(name) LIKE '".strtolower($needle)."%'"
    ."UNION SELECT name, id, 'community' AS tablename FROM communities WHERE lower(name) LIKE '".strtolower($needle)."%'"
    ."UNION SELECT name, id, 'district' AS tablename FROM districts WHERE lower(name) LIKE '".strtolower($needle)."%'"
    ."UNION SELECT name, id, 'region' AS tablename FROM regions WHERE lower(name) LIKE '".strtolower($needle)."%'"
    ."UNION SELECT name, id, 'province' AS tablename FROM provinces WHERE lower(name) LIKE '".strtolower($needle)."%'"
    ."ORDER BY name LIMIT 10"

this is my SQL.

do u need more information?

thanks

Answer 1

DISTINCT

SELECT DISTINCT ON (name) name, id, tablename 
FROM (
    SELECT name, id, 'place' AS ... 
    UNION ... 
    UNION ... 
    UNION ... 
    UNION ...) AS subQuery
ORDER BY name LIMIT 10

Just specify to only do distinct on the name by using your query as a sub-query.

Do the limit and ordering during the distinct, otherwise the distinct might discard dupes and not give up the full 10 rows in the correct sorted order.