SQL SELECT individual rows from a table with multiple columns, ignoring the column order (value)

Question

I have a People table (First_Name, Last_Name). This table has entries that are duplicated, as in the example (not all rows are duplicated):

First_Name  Last_Name
John        Smith
Alec        Baldwin
Smith       John
Angelo      Gordon
Mary        Bush
Bush        Mary

How to choose all the people? The final result of the query, John Smith should appear only once (do not import it if there is John Smith or Smith John in the last query).

Thank.

Answer 1

Just select an order and apply it to everyone. Then use a join that will eliminate duplicates anyway

select FirstName,LastName from People where FirstName <= LastName
union
select LastName,FirstName from People where LastName < FirstName

Answer 2

This is one way to do this using almost every taste of SQL.

DECLARE @Names TABLE (
  First_Name VARCHAR(32)
  , Last_Name VARCHAR(32)
)  

INSERT INTO @Names VALUES ('John', 'Smith')
INSERT INTO @Names VALUES ('Alec', 'Baldwin')
INSERT INTO @Names VALUES ('Smith', 'John')
INSERT INTO @Names VALUES ('Angelo', 'Gordon')
INSERT INTO @Names VALUES ('Mary', 'Bush')
INSERT INTO @Names VALUES ('Bush', 'Mary')

Using JOIN

SELECT  n1.*
FROM    @Names n1
        LEFT OUTER JOIN @Names n2 ON n2.First_Name = n1.Last_Name
                                     AND n2.Last_Name = n1.First_Name
                                     AND n2.First_Name < n1.First_Name
WHERE   n2.First_Name IS NULL                                     

or NOT EXISTS

SELECT  n1.*
FROM    @Names n1
WHERE   NOT EXISTS (
          SELECT  *  
          FROM    @Names n2 
          WHERE   n2.First_Name = n1.Last_Name
                  AND n2.Last_Name = n1.First_Name
                  AND n2.First_Name < n1.First_Name
        )

Answer 3

, ...

WITH People (Firstname, Lastname)
AS
(
    SELECT 'John' AS Firstname, 'Smith' AS Lastname UNION
    SELECT 'John' AS Firstname, 'Smith' AS Lastname UNION
    SELECT 'Alec' AS Firstname, 'Baldwin' AS Lastname UNION
    SELECT 'Smith' AS Firstname, 'John' AS Lastname UNION
    SELECT 'John' AS Firstname, 'Smith' AS Lastname UNION
    SELECT 'Angelo' AS Firstname, 'Gordon' AS Lastname UNION
    SELECT 'Mary' AS Firstname, 'Bush' AS Lastname UNION
    SELECT 'Bush' AS Firstname, 'Mary' AS Lastname
)
SELECT p1.* FROM People p1 
LEFT OUTER JOIN People p2 ON p2.Firstname = p1.Lastname AND p2.Lastname = p1.Firstname AND p2.Firstname < p1.Firstname 
WHERE p2.Firstname IS NULL  

Answer 4

It uses a solution using Oracle features. Other SQL tastes will have the same or very similar functions:

SQL> select * from t23
  2  /

FIRST_NAME                     LAST_NAME
------------------------------ ------------------------------
John                           Smith
Alec                           Baldwin
Smith                          John
Angelo                         Gordon
Mary                           Bush
Bush                           Mary

6 rows selected.

SQL> select distinct least(first_name, last_name)
  2                  , greatest(first_name, last_name)
  3  from t23
  4  /

LEAST(FIRST_NAME,LAST_NAME)    GREATEST(FIRST_NAME,LAST_NAME)
------------------------------ ------------------------------
Alec                           Baldwin
Bush                           Mary
John                           Smith
Angelo                         Gordon

SQL> 

Answer 5

I think this can work in MS-SQL

select * from people

where (FirstName + "," + LastName) <> (LastName + "," + FirstName)

Answer 6

Another sugestion

Temporary table:

DECLARE @Names TABLE (
  First_Name VARCHAR(32)
  , Last_Name VARCHAR(32)
)  

INSERT INTO @Names VALUES ('John', 'Smith')
INSERT INTO @Names VALUES ('Alec', 'Baldwin')
INSERT INTO @Names VALUES ('Smith', 'John')
INSERT INTO @Names VALUES ('Angelo', 'Gordon')
INSERT INTO @Names VALUES ('Mary', 'Bush')
INSERT INTO @Names VALUES ('Bush', 'Mary')

Using CASE

SELECT DISTINCT 
    CASE WHEN First_Name <= Last_Name THEN First_Name ELSE Last_Name END AS First_Name,
    CASE WHEN First_Name <= Last_Name THEN Last_Name ELSE First_Name END AS Last_Name
FROM @Names