CODEWITHSUNDEEP
sql
ss1731
ss1731

Reputation: 15

SQL: Selecting rows from non unique column values once partitioned by another column

Using SQL here. Trying to select all rows where the column value is unique within that specific partition.

Have tried:

 select * 
   from dataTable 
  where value in ( select value 
                     from dataTable 
                    group by tv_id, value 
                   having count(*) > 1)

but it returns the full table-- i think the issue is that the values for many of tv_ids are identical and overlap.

What I have:

tv_id value 1 1 1 2 1 2 1 3 2 1 2 1 2 2 2 3 2 4 3 1 3 1 3 2

What I want:

tv_id value 1 2 1 2 2 1 2 1 3 1 3 1

I have a bunch of tv_ids and essentially, I only want the rows where the value is not unique within each tv_id. Ex: I don't want tv_id, value: 3, 2 because it is the only combination in the data.

Thanks in advance!

Upvotes: 1

Views: 449

Answers (1)

James
James

Reputation: 3015

Maybe something like this does the trick

Oracle Option

I include this oracle version because it enables you to understand better what are you querying.

select tv_id, value  
from dataTable
where (tv_id, value)  in (
  select tv_id, value
  from dataTable
  group by tv_id, value
  having count(1) > 1
)

SQL

But this is a standard sql version that will work with almost any database engine

select tv_id, value  
from dataTable d1
  join (
    select tv_id, value
    from dataTable
    group by tv_id, value
    having count(1) > 1
  ) d2
  on d1.tv_id=d2.tv_id
 and d1.value=d2.value

You need to query the same table twice because the group by makes a distinct in your data, so you won't retrieve duplicated rows as you show in your expected output.

Upvotes: 1

Related Questions