CODEWITHSUNDEEP
sqloracle-databaseoracle11g
zundarz
zundarz

Reputation: 1594

How to get query to return rows where first three characters of one row match another row?

Here's my data: 

with first_three as 
    (
    select 'AAAA' as code  from dual union all
    select 'BBBA' as code  from dual union all
    select 'BBBB' as code  from dual union all
    select 'BBBC' as code  from dual union all
    select 'CCCC' as code  from dual union all
    select 'CCCD' as code  from dual union all
    select 'FFFF' as code  from dual union all
    select 'GFFF' as code  from dual )
    select substr(code,1,3) as r1 
    from first_three
    group by  substr(code,1,3)
    having count(*) >1

This query returns the characters that meet the cirteria. Now, how do I select from this to get desired results? Or, is there another way?

Desired Results

BBBA
BBBB
BBBC
CCCC
CCCD

Upvotes: 0

Views: 86

Answers (3)

Sai
Sai

Reputation: 689

     select res from (select res,count(*) over 
    (partition by substr(res,1,3) order by null) cn from table_name) where cn>1;

Upvotes: 1

MT0
MT0

Reputation: 168416

WITH code_frequency AS (
  SELECT code,
         COUNT(1) OVER ( PARTITION BY SUBSTR( code, 1, 3 ) ) AS frequency
  FROM   table_name
)
SELECT code
FROM   code_frequency
WHERE  frequency > 1

Upvotes: 3

PinnyM
PinnyM

Reputation: 35531

WITH first_three AS (
  ...
) 
SELECT * 
FROM first_three f1
WHERE EXISTS (
    SELECT 1 FROM first_three f2 
    WHERE f1.code != f2.code 
        AND substr(f1.code, 1, 3) = substr(f2.code, 1, 3)
)

Upvotes: 2

Related Questions