Check if two columns are equal for all rows per group

Question

Assuming a DB like this:

Date | Attribute1 | Attribute2 | ... | AttributeN
-------------------------------------------------
1    | A          | C          | ... | ...
1    | B          | C          | ... | ...
2    | A          | A          | ... | ...
2    | B          | B          | ... | ...
2    | A          | A          | ... | ...
3    | B          | B          | ... | ...
3    | A          | A          | ... | ...
4    | B          | C          | ... | ...
4    | A          | A          | ... | ...

I am trying to find for which unique dates (they are actual dates in the real case but I don't think that matters), ALL elements of Attribute1 are equal to their corresponding elements in Attribute2. The result for the example data above would be

Date
----
2
3

Because for each record that has date equal to 2 (and the same for 3) , Attribute1 is equal to Attribute2. 4 is not returned because although the last record in the sample does meet the criterion (since A equals A), the second last record does not (since B does not equal C).

I could not work out how to write this query, I was hoping for some aggregate function (shown as ALL(...) in the code below) that would allow me to write something like:

SELECT Date
FROM myTable
GROUP BY Date
HAVING ALL(Attribute1 = Attribute2)

Is there such a function? Otherwise is there a clever way to this using COUNT maybe?

Lukasz Szozda · Accepted Answer · 2015-12-14T15:16:02.360

4

You can use HAVING and CASE:

SELECT [Date]
FROM #tab
GROUP BY [Date]
HAVING SUM(CASE WHEN Attribute1 = Attribute2 THEN 0 ELSE 1 END) = 0

LiveDemo

Otherwise is there a clever way to this using COUNT maybe?

Why not :) Version with COUNT:

SELECT [Date]
FROM #tab
GROUP BY [Date]
HAVING COUNT(CASE WHEN Attribute1 <> Attribute2 THEN 1 END) = 0

LiveDemo2

edited Dec 14 '15 at 15:16

answered Dec 14 '15 at 15:13

Lukasz Szozda

162,964
23
234
275

1

This (the `SUM` solution) seems to be the fastest for my data out of the three solutions offered here – Dan Dec 14 '15 at 15:15

score 3 · Answer 2 · edited Dec 14 '15 at 15:08

3

Perhaps this:

select Date from Test
Except
Select Date from Test where Attribute1 <> Attribute2

edited Dec 14 '15 at 15:08

Lukasz Szozda

162,964
23
234
275

answered Dec 14 '15 at 15:06

Polux2

552
3
12

There is no need for `DISTINCT`. Check [doc](https://msdn.microsoft.com/en-us/library/ms188055.aspx) `EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.` – Lukasz Szozda Dec 14 '15 at 15:09

score 1 · Answer 3 · answered Dec 14 '15 at 15:04

The logic is easier if you turn it around. You want all unique DATE values where there isn't a row where Attribute1 is different to Attribute2:

SELECT DISTINCT [Date] FROM myTable
WHERE [Date] NOT IN (
   SELECT [Date] FROM myTable
   WHERE Attribute1 != Attribute2)

Check if two columns are equal for all rows per group

3 Answers3