Advertisement
Guest User

Nicks Solution Tweaked

a guest
Oct 21st, 2011
137
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.64 KB | None | 0 0
  1. WITH set_sizes AS (
  2.   SELECT x, COUNT(*) AS set_size  -- "size" is a reserved keyword in Oracle
  3.   FROM t
  4.   GROUP BY x
  5. )
  6. , intersection_sizes AS (
  7.   SELECT
  8.       sub.x     sub_x
  9.     , super.x   super_x
  10.     , COUNT(*)  intersection_size
  11.   FROM
  12.                 t sub
  13.     INNER JOIN  t super
  14.       ON  sub.y = super.y
  15.       AND sub.x < super.x
  16.   GROUP BY
  17.       sub.x
  18.     , super.x
  19. )
  20. SELECT ss.x AS sub_x, decode(ss.x,xs.sub_x,xs.super_x,xs.sub_x) AS super_x
  21. FROM
  22.               set_sizes           ss
  23.   INNER JOIN  intersection_sizes  xs
  24.     ON  (ss.x = xs.sub_x OR ss.x = xs.super_x)
  25.     AND ss.set_size = xs.intersection_size
  26. ;
  27.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement