Question 90049 in PostgreSQL Question posted in
The official documentation can be found here.

Querying all unique city names in one state and no where else – Postgresql

I am trying to query all unique city names for one particular state without pulling other states. This is what I am trying to do:

SELECT DISTINCT city_name
FROM table
WHERE state = 'California';

This does not work because there is a San Diego in other states aside from California. How would I query this so that it only gives me city_names unique only to California and not other US states? Can someone please help? Thank you for your time.

SELECT DISTINCT city_name
FROM table
WHERE state = 'California';

The desired output is unique city names only in California and no where else.

Tags:

2

Answers


  1. 0

    This query is using a subquery in the WHERE clause to filter the results from the table "mytable" (aliased as "t1"), by checking that the city_name does not exist in the table with a different state, the subquery uses a NOT EXISTS clause to check that the city_name does not exist in the table "mytable" (aliased as "t2") with a different state than ‘California’.

    select distinct t1.city_name from mytable t1
where state = 'California'
and not exists (select 1 from mytable t2 
                where state != 'California'
                and t2.city_name = t1.city_name)
    Login or Signup to reply.
  2. 0

    You can use aggregation and set the conditions in the HAVING clause:

    SELECT city_name
FROM tablename
GROUP BY city_name
HAVING COUNT(*) = SUM((state = 'California')::int);

    SUM((state = 'California')::int) will be 1 only for city_names that can be found in 'California' and it will be 0 for all other city_names.

    COUNT(*) will be 1 only for city_names that exist in only 1 state (and it is never 0).

    The only case that these expressions are equal is when they are both 1.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search