# Select pairs (a,b) excluding (b,a) with same value

For a given structure like this:

``````+----+----------+
| ID |   Name   |
+----+----------+
| a  | ZZZZZZZZ |
| b  | YYYYYYYY |
| c  | WWWWWWWW |
| d  | ZZZZZZZZ |
| e  | YYYYYYYY |
| f  | WWWWWWWW |
+----+----------+
``````

Get the list of pairs (ID, ID2) with same Name, excluding all records where (ID2, ID) has the corresponding (ID, ID2).

``````+----+----------+-----+
| ID |   Name   | ID2 |
+----+----------+-----+
| a  | ZZZZZZZZ |  d  |
| b  | YYYYYYYY |  e  |
| c  | WWWWWWWW |  f  |
+----+----------+-----+
``````

I've found this solution, but I wonder if there is a more "academic" solution.

``````;WITH Pair AS
(
SELECT
CASE WHEN p1.Id > p2.Id THEN p2.Id ELSE p1.Id END as ID,
CASE WHEN p1.Id > p2.Id THEN p1.Id ELSE p2.Id END as ID2,
p1.Name
FROM Pairs p1
LEFT JOIN Pairs p2
ON p1.Name = p2.Name
AND p1.Id <> p2.Id
)
SELECT DISTINCT ID, ID2, Name
FROM Pair;
``````

Rextester

``````CREATE TABLE Pairs(Id varchar(5), Name varchar(20));
INSERT INTO Pairs VALUES
('a','ZZZZZZZZ'),
('b','YYYYYYYY'),
('c','WWWWWWWW'),
('d','ZZZZZZZZ'),
('e','YYYYYYYY'),
('f','WWWWWWWW');
``````
• How are you sure there will only ever be exactly two Id values for any given Name, and that they will always be unique? Jan 22, 2017 at 22:43
• Hi @AaronBertrand, in fact it is similar to another question I've worked today. But the Id was a numeric value and Gordon Linoff has pointed me out about use '<'. Obviously I didn't realize that it can be used for whatever field type. Jan 22, 2017 at 23:13

``````SELECT
• It is obvious that the `<` operator id defined for numeric types, same for date-time types and also true for character types. Jan 22, 2017 at 21:44