CODEWITHSUNDEEP
sql-server
buzzard51
buzzard51

Reputation: 1467

find pairs of rows that have same column value

Using SQL Server, I have a database table that models a collection of devices. Each device controls one or more regions, and each region can be either offline or online.

I cannot have a situation where the same region is online on more than one device.

table "FEP" schema is: 

region_address [int]
region         [int]
online         [bit]

sample data:

region_address  region   online      
3001100         100      0
3002100         100      1
3002200         200      0
3002241         241      0
3001123         123      0
3003123         123      1
3928999         999      1
3921999         999      1

I need this query to list region 999 as being online on two (or more) listed devices.

I use a simple select * from fep order by region to visually see if any two successive rows have online = 1, but this doesn't identify ONLY conflicting regions.

Upvotes: 1

Views: 539

Answers (1)

Rochel L Brezner
Rochel L Brezner

Reputation: 36

I would do this a bit simpler

SELECT region
FROM FEP
WHERE online = 1
GROUP BY region
HAVING COUNT(*) > 1

The issue with doing Having count(distinct online)=1 is let's say there are 3 devices, 2 are online and 1 is not. So count(distinct online) would equal 2 but there are still multiple devices online and you would not catch that. The above would account for that, and it would also account for devices, even if there were NULL values (provided that there were at least 2 devices online as was the criteria)

Upvotes: 2

Related Questions