CODEWITHSUNDEEP
sqloracle-database
MvdD
MvdD

Reputation: 23436

Finding rows that map to the same value

I have a table with mappings:

|id|from|to|
|1 | 12 |AB|
|2 | 34 |CD|
|3 | 56 |AB|
|4 | 67 |EF|
|5 | 89 |CD|
|6 | 01 |GH|
|7 | 23 |CD|

How do I find rows that map to the same to value?

So the query would ideally result in something like:

|id|from|to|
|1 | 12 |AB|
|3 | 56 |AB|
|2 | 34 |CD|
|5 | 89 |CD|
|7 | 23 |CD|

Is there an efficient way to do this in SQL?

Upvotes: 0

Views: 63

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can also do this with exists:

select t.*
from t
where exists (select 1 from t t2 where t2.to = t.to and t2.id <> t.id);

I offer this because under some circumstances (particularly with an index on (to, id), this may be the fastest approach.

Upvotes: 1

user5683823
user5683823

Reputation:

You can use the analytic count() function to show how many rows have the same to value on each row. Then you can use that in an outer query to filter out the rows with a count of 1.

This approach has the advantage that the base data is read only once (as opposed to twice in many other approaches). Of course, this is only important if execution time matters - if you have a lot of data, and/or if you must run this query frequently.

select id, from, to
from   (
         select id, from, to, count(*) over (partition by to) as cnt
         from   my_table
       )
where  cnt > 1
;

Upvotes: 2

Meqenaneri Vacharq
Meqenaneri Vacharq

Reputation: 99

select * from table_name order by to,from

Upvotes: -3

juergen d
juergen d

Reputation: 204746

select *
from your_table
where "to" in (select "to" from your_table group by "to" having count(*) > 1)
order by "to"

Upvotes: 1

Related Questions