7

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');
2
  • How are you sure there will only ever be exactly two Id values for any given Name, and that they will always be unique? – Aaron Bertrand Jan 22 '17 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. – McNets Jan 22 '17 at 23:13
10
SELECT
    p1.Id,p2.Id,p1.Name 
FROM Pairs p1
     JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id < p2.Id
3
  • Works this solution for whatever field type? – McNets Jan 22 '17 at 21:41
  • 1
    It is obvious that the < operator id defined for numeric types, same for date-time types and also true for character types. – David דודו Markovitz Jan 22 '17 at 21:44
  • 1
    For character data be aware of the collation as this defines what "less than" means. – Michael Green Jan 23 '17 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.