CODEWITHSUNDEEP
sqlsqlite
JHarley1
JHarley1

Reputation: 2112

SQL Query - Check for Two Distinct Values

Given the below data set I want to run a query to highlight any 'pairs' that do not consist of a 'left' and 'right'.

+---------+-----------+---------------+----------------------+
| Pair_Id | Pair_Name | Individual_Id | Individual_Direction |
+---------+-----------+---------------+----------------------+
|       1 | A         | A1            | Left                 |
|       1 | A         | A2            | Right                |
|       2 | B         | B1            | Right                |
|       2 | B         | B2            | Left                 |
|       3 | C         | C1            | Left                 |
|       3 | C         | C2            | Left                 |
|       4 | D         | D1            | Right                |
|       4 | D         | D2            | Left                 |
|       5 | E         | E1            | Left                 |
|       5 | E         | E2            | Right                |
+---------+-----------+---------------+----------------------+

In this instance Pair 3 'C' has two lefts. Therefore, I would look to display the following:

+---------+-----------+---------------+----------------------+
| Pair_Id | Pair_Name | Individual_Id | Individual_Direction |
+---------+-----------+---------------+----------------------+
|       3 | C         |  C1           | Left                 |
|       3 | C         |  C2           | Left                 |
+---------+-----------+---------------+----------------------+

Upvotes: 1

Views: 118

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269533

You can simply use not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.pair_id = t.pair_id and
                        t2.Individual_Direction <> t.Individual_Direction
                 ) ;

With an index on (pair_id, Individual_Direction), this should not only be the most concise solution but also the fastest.

If you want to be sure that there are pairs (the above returns singletons):

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.pair_id = t.pair_id and
                        t2.Individual_Direction <> t.Individual_Direction
                 ) and
      exists (select 1
              from t t2
              where t2.pair_id = t.pair_id and
                    t2.Individual_ID <> t.Individual_ID
            );

You can also do this using window functions:

select t.*
from (select t.*,
             count(*) over (partition by pair_id) as cnt,
             min(status) over (partition by pair_id) as min_status,
             max(status) over (partition by pair_id) as max_status
      from t
     ) t
where cnt > 1 and min_status <> max_status;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

One option uses aggregation:

WITH cte AS (
    SELECT Pair_Name
    FROM yourTable
    WHERE Individual_Direction IN ('Left', 'Right')
    GROUP BY Pair_Name
    HAVING MIN(Individual_Direction) = MAX(Individual_Direction)
)

SELECT *
FROM yourTable
WHERE Pair_Name IN (SELECT Pair_Name FROM cte);

The HAVING clause used above asserts that a matching pair has both a minimum and maximum direction which are the same. This implies that such a pair only has one direction.

As is the case with Gordon's answer, an index on (Pair_Name, Individual_Direction) might help performance:

CREATE INDEX idx ON yourTable (Pair_Name, Individual_Direction);

Upvotes: 3

RandyMcKay
RandyMcKay

Reputation: 326

There should be an elegant way of using window function than what I wrote:

WITH ranked AS
(
 SELECT *, RANK() OVER(ORDER BY Pair_Id, Pair_Name, Individual_Direction) AS r
     FROM pairs
),
    counted AS
(
SELECT Pair_Id, Pair_Name, Individual_Direction,r, COUNT(r) as times FROM ranked 
GROUP BY Pair_Id, Pair_Name, Individual_Direction, r
HAVING COUNT(r) > 1
)
SELECT ranked.Pair_Id, ranked.Pair_Name, ranked.Individual_Id, ranked.Individual_Direction FROM ranked
RIGHT JOIN counted 
ON ranked.Pair_Id=counted.Pair_Id 
AND ranked.Pair_Name=counted.Pair_Name 
AND ranked.Individual_Direction=counted.Individual_Direction

Upvotes: 0

Related Questions