What SQL returns duplicates in first column where

Question

Question is very similiar to this find duplicates but I'd like to find only those duplicate id with code different than 'ROME' and at least one name is 'ROME'.

I want desired results because:

1. ID is duplicate.
2. At least one origin is 'ROME'
3. Remaining rows for that ID are NOT 'ROME'

Table 

ID   ORIGIN
-----------
1    ROME
1    ROME
2    ROME
2    LODI
3    ASTI
4    PISA
4    BARI

Desired Results
ID   ORIGIN
-----------
 2   ROME
 2   LODI 

Answer 1

SELECT t0.id, t0.origin FROM Table t0 WHERE t0.id IN
(SELECT distinct(t1.id) FROM Table t1 JOIN Table t2 ON t1.id = t2.id WHERE t1.id > 
(SELECT t2.id FROM Countries t2 WHERE t1.name = t2.name LIMIT 1));

Output

2 Rome
2 Lodi

This produced the results you're looking for. I'm not sure if it has any errors for a different data set. But this should get you going in the right direction.

Answer 2

SELECT id,
       origin
FROM (
    SELECT id, 
           origin, 
           count(*) over (partition by id) as id_count,
           count(case when origin = 'ROME' then origin else null end) over (partition by id) as rome_count,
           count(case when origin <> 'ROME' then origin else null end) over (partition by id) as non_rome_count,
           row_number() over (partition by id) as rn

    FROM stuff
) t
WHERE rome_count > 0 
  and non_rome_count > 0
  and id_count > 1

Answer 3

SELECT
    id,
    origin
FROM
    My_Table T1
WHERE
    EXISTS (SELECT * FROM My_Table T2 WHERE T2.id = T1.id AND T2.origin = 'ROME') AND
    EXISTS (SELECT * FROM My_Table T3 WHERE T3.id = T1.id AND T3.origin <> 'ROME')

Answer 4

CREATE GLOBAL TEMPORARY TABLE Test
(
    Id int,
    Origin varchar(100)
)

INSERT INTO #Test
SELECT  1,    'ROME' FROM DUAL UNION ALL
SELECT  1,    'ROME' FROM DUAL UNION ALL
SELECT  2,    'ROME' FROM DUAL UNION ALL
SELECT  2,    'LODI' FROM DUAL UNION ALL
SELECT  3,    'ASTI' FROM DUAL UNION ALL
SELECT  4,    'PISA' FROM DUAL UNION ALL
SELECT  4,    'BARI'

SELECT T.Id, T.Origin FROM #Test T
JOIN Test T2 ON T.Id = T2.Id
WHERE T.Origin = 'ROME' AND T2.Origin != 'ROME' OR T.Origin != 'ROME' AND T2.Origin = 'ROME'

It was asked in a comment if this could be written more generically. It cannot, otherwise we would not get the correct results (PISA and BARI would trip the WHERE on Table1.Origin != Table2.Origin)