How do you create a query will show all data that contains the same value in name. Is this possible. This was similar to my first question yesterday. Using only the name in the query

For example - table 1:

Desired output:

CodePudding user response：

You can use COUNT() Analytic function such as

WITH t AS
(
 SELECT t.*, 
        COUNT(*) OVER (PARTITION BY REGEXP_SUBSTR(LOWER(t.name),'[^ ] ')) AS cnt
   FROM table1 t
)
SELECT ID, Name, Age
  FROM t
 WHERE cnt > 1  
 ORDER BY ID

Demo

where need to think case insensitively, and extract the first portion of name in order to seperate name and surname provided only the first name is matter of interest even if there are name-surname combinations of more than two words.

CodePudding user response：

You can do like that.

select * from #temp where Names in (
    select Names from #temp
    group by Names having count(*) > 1
)

CodePudding user response：

You can also use EXISTS. Convert both names to upper case to get both 'john' and 'John' rows.

select a.* 
from table_1 a
where exists(select 1
             from table_1 b
             where a.id <> b.id and UPPER(a.name) = UPPER(b.name))

CodePudding user response：

SELECT * FROM Table1 
  WHERE Name IN (
    SELECT DISTINCT Name
      FROM Table1
      GROUP BY Name
      HAVING COUNT(*) > 1
 )
 ORDER BY Name

with case sensitive db

 SELECT * FROM Table1 WHERE LOWER(Name) IN (
    SELECT DISTINCT LOWER(Name)
      FROM Table1
      GROUP BY LOWER(Name)
      HAVING COUNT(*) > 0
 )
 ORDER BY Name

CodePudding user response：

SELECT * FROM Persenel
  WHERE LOWER(Name) IN (
    SELECT DISTINCT LOWER(Name)
      FROM Persenel
      GROUP BY LOWER(Name)
      HAVING COUNT(LOWER(PName)) > 1
 )
ORDER BY Name