StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Okay, here's my attempt. It might be possible to implement this logic in a way that doesn't require 5 accesses to the same table, but I can't think of it right now.</p> <p>The logic here is to first eliminate duplicate objects, then count the remaining IDs. The <code>NOT IN</code> subquery represents objects that have a matching object with a smaller ID. The subquery joins the parameters of two objects t1 and t2, then counts how many parameters matched for each t1/t2 pair. If the number of matching parameters is the same as the number of parameters in t1 and in t2, then t2 and t1 are matches and we should exclude t1 from the resultset.</p> <pre><code>DECLARE @tab TABLE (ID int, parameter varchar(2)); INSERT INTO @tab SELECT 1, 'A' UNION ALL SELECT 1, 'B' UNION ALL SELECT 2, 'A' UNION ALL SELECT 3, 'A' UNION ALL SELECT 3, 'B' UNION ALL SELECT 4, 'A' UNION ALL SELECT 5, 'C' UNION ALL SELECT 5, 'D'; SELECT COUNT(DISTINCT t.ID) AS num_groups FROM @tab AS t WHERE t.ID NOT IN (SELECT t1.ID AS ID1 FROM @tab AS t1 INNER JOIN @tab AS t2 ON t1.ID > t2.ID AND t1.parameter = t2.parameter GROUP BY t1.ID, t2.ID HAVING COUNT(*) = (SELECT COUNT(*) FROM @tab AS dupe WHERE dupe.ID = t1.ID) AND COUNT(*) = (SELECT COUNT(*) FROM @tab AS dupe WHERE dupe.ID = t2.ID) ); </code></pre> <p>Result on SQL Server 2008 R2:</p> <pre><code>num_groups 3 </code></pre> <p>As for objects with 0 parameters, it depends on how they're stored, but generally, you'd just need to add one to the answer above if there are any objects with 0 parameters.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload