Select cities with the same name in different states

Question

Select cities with the same name in different states

Navigation

#1 by (4 votes)

3

I have the table (result of a query) with the cities and their respective state ...

...Nowyouonlyhavetodisplaycitieswithequalnamesanddifferentstates:

How do I do the command?

sql oracle pl-sql

asked by anonymous 08.12.2017 / 14:02

1 answer

Pointer is lost at function output Error converting String to Double

score 4 · Accepted Answer

It would be something like this, first you get only those that have more than one recurrence in the table, and then make a query by name, as you did not know the name of your table just replace the [sua_tabela] tag with the name of the table and test:

SELECT
    *
FROM [sua_tabela]
WHERE cidade = (
SELECT
    cidade
FROM 
(SELECT 
    COUNT(*) AS qtd,
    cidade
from [sua_tabela]
GROUP BY cidade
HAVING qtd > 1) as aux);