The Oracle (tm) Users' Co-Operative FAQ

How do I determine which row has a reciprocal value pair in a SQL Statement?


Author's name: Frank Zhou

Author's Email:zhou328@comcast.net

Date written: 12th December 2006

Oracle version(s): 10.2

How do I determine which row has a reciprocal value pair in a SQL Statement?

Back to index of questions


The following SQL query pattern can be used to determine which row has a reciprocal value pair. (The extra requirement for this SQL pattern is that the timing of the reciprocal value pairs much be within 1 day of each other)

SQL create table t_r as

select 38 as v1, 63 as v2, sysdate as time from dual

union all

select 63 as v1, 38 as v2, sysdate - 1/24 as time from dual

union all

select 28 as v1, 28 as v2, sysdate as time from dual

union all

select 28 as v1, 28 as v2, sysdate - 1/24 as time from dual

union all

select 15 as v1, 49 as v2, sysdate as time from dual

union all

select 99 as v1, 99 as v2, sysdate as time from dual

union all

select 99 as v1, 99 as v2, sysdate -2 as time from dual

union all

select 77 as v1, 88 as v2, sysdate as time from dual

union all

select 77 as v1, 88 as v2, sysdate -2/24 as time from dual

 

Table created.

 

SQL> select * from t_r;

 

V1 V2 TIME

--- --- ---------

38 63 13-DEC-06 --------> "T"

63 38 13-DEC-06 --------> "T"

28 28 13-DEC-06 --------> "T"

28 28 13-DEC-06 --------> "T"

15 49 13-DEC-06 ->Doesn't has a reciprocal value pair, so this is "F"

99 99 13-DEC-06 --->Date range are not within one day, so this is "F"

99 99 11-DEC-06 --->Date range are not within one day, so this is "F"

77 88 13-DEC-06 --->Doesn't has a reciprocal, so this is "F"

77 88 13-DEC-06 --->Doesn't has a reciprocal, so this is "F"

 

SELECT v1, v2, time,

CASE WHEN v1 = v2

THEN

CASE WHEN COUNT(*) OVER (PARTITION BY V1,V2 ORDER BY time

RANGE BETWEEN NUMTODSINTERVAL(1, 'day') PRECEDING

AND NUMTODSINTERVAL(1, 'day') FOLLOWING )>= 2

THEN 'T'

ELSE 'F' END

ELSE

CASE WHEN COUNT(*) OVER (PARTITION BY

GREATEST(V1, V2),LEAST(V1,V2) ORDER BY time

RANGE BETWEEN NUMTODSINTERVAL(1, 'day') PRECEDING

AND NUMTODSINTERVAL(1, 'day') FOLLOWING ) -

COUNT(*) OVER (PARTITION BY V1, V2 ORDER BY time

RANGE BETWEEN NUMTODSINTERVAL(1, 'day') PRECEDING

AND NUMTODSINTERVAL(1, 'day') FOLLOWING )>= 1

THEN 'T'

ELSE 'F' END

END AS Reciprocals_Flag

FROM t_r

 

V1 V2 TIME Reciprocals_Flag

--- --- --------- ----------------

15 49 13-DEC-06 F

28 28 13-DEC-06 T

28 28 13-DEC-06 T

38 63 13-DEC-06 T

63 38 13-DEC-06 T

77 88 13-DEC-06 F

77 88 13-DEC-06 F

99 99 11-DEC-06 F

99 99 13-DEC-06 F

 

9 rows selected.

 

SQL> spool off


 

Back to top

Back to index of questions