Question: I'm struggling with a tricky SQL query that I'm trying to write. Have a look at the following table:

```
+---+---+
| A | B |
+---+---+
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 2 |
| 3 | 3 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
+---+---+

```

Now, from this table, I essentially want a list of all As which have the exact same set of Bs and give each set an incrementing ID.

Hence, the output set for the above would be:

```
+---+----+
| A | ID |
+---+----+
| 1 |  1 |
| 3 |  1 |
| 2 |  2 |
| 4 |  2 |
+---+----+

```

Thanks.

Edit: If it helps, I have a list of all distinct values of B that are possible in another table.

Edit: Thank you so much for all the innovative answers. Was able to learn a lot indeed.

Answer: 
Here is mathematical trick to solve your tricky select:

```
with pow as(select *, b * power(10, row_number() 
              over(partition by a order by b)) as rn from t)
select a, dense_rank() over( order by sum(rn)) as rn 
from pow
group by a
order by rn, a

```

Fiddle <http://sqlfiddle.com/#!3/6b98d/11>

This of course will work only for limited distinct count as you will get overflow. Here is more general solution with strings:

```
select a, 
dense_rank() over(order by (select  '.' + cast(b as varchar(max))
                            from t t2 where t1.a = t2.a
                            order by b
                            for xml path(''))) rn
from t t1
group by a
order by rn, a

```

Fiddle <http://sqlfiddle.com/#!3/6b98d/29>
Here's a long winded approach, by finding sets with the same elements (using `EXCEPT` bidirectionally to eliminate, and just done a half diagonal cartesian product), then pairing equal sets up, stamping each pair with a `ROW_NUMBER()`, before unpivoting the pairs of `A's` into to your final output where the equivalent sets are projected as rows which have the same `id`.

```
WITH joinedSets AS
(
    SELECT t1.A as t1A, t2.A AS t2A
    FROM MyTable t1
    INNER JOIN MyTable t2
    ON t1.B = t2.B
        AND t1.A < t2.A
),
equalSets AS
(
    SELECT js.t1A, js.t2A, ROW_NUMBER() OVER (ORDER BY js.t1A) AS Id
    FROM joinedSets js
    GROUP BY js.t1A, js.t2A
    HAVING NOT EXISTS ((SELECT mt.B FROM MyTable mt WHERE mt.A = js.t1A)
           EXCEPT (SELECT mt.B FROM MyTable mt WHERE mt.A = js.t2A))
         AND NOT EXISTS ((SELECT mt.B FROM MyTable mt WHERE mt.A = js.t2A) 
           EXCEPT (SELECT mt.B FROM MyTable mt WHERE mt.A = js.t1A))
)
SELECT A, Id
FROM equalSets
UNPIVOT
(
    A
    FOR ACol in (t1A, t2A)
) unp;

```

[SqlFiddle here](http://sqlfiddle.com/#!6/501aa/31)

As it stands, this solution will only work with pairs of sets, not triples etc. A general `NTuple` type solution is probably possible (but beyond my brain right now).