CODEWITHSUNDEEP
sqloracle-database
jfin3204
jfin3204

Reputation: 709

Select 1 record where two records have the the same values in different columns

I have a table that contains two columns.

Table snap

in the first two rows the values for the columns are reversed, how would I select one record for every instance where one STATION_1_I equals another records STATION_2_I and its STATION_2_I equals STATION_1_I.

Upvotes: 1

Views: 1573

Answers (2)

David Jashi
David Jashi

Reputation: 4511

select a,b from
(
select 
(case when a<b then a else b end) as a,
(case when a>b then a else b end) as b
from (
select station_1_I as a, station_2_I as b from MyTable
union all
select station_2_I, station_1_I from MyTable
) having count(*)=2 group by a,b
) group by a,b

Upvotes: 2

gbn
gbn

Reputation: 432657

INTERSECT will remove duplicates for you

select "station_1_I", "station_2_I" from mytable
intersect
select "station_2_I", "station_1_I" from mytable 
             where "station_2_I" < "station_1_I"

SQL Fiddle

Upvotes: 3

Related Questions