SQL SELECT distinct rows from a table by multiple columns ignoring columns order (significance)

Question

I have a table People (First_Name, Last_Name). This table has records 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 do I select all distinct people? In the final output of the query John Smith should appear only once (it’s not import if in the final query there is John Smith or Smith John).

Thank you.

Damien_The_Unbeliever · Accepted Answer · 2010-08-26 08:59:49Z

5

Just pick an ordering and apply it across everyone. Then use a union that will eliminate duplicates anyway

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

answered Aug 26, 2010 at 8:59

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tobiasopdenbrouw Over a year ago

Nice one. Would you use this version in RL over the answer I posted? (And why?)

Damien_The_Unbeliever Over a year ago

@Tobias - Well, it was the first answer I thought of, and I didn't see anyone else posting similar. I'd note that yours does depend on a special character (" ") not appearing within these columns, which may or may not be relevant. Depending on the size of the table, and the intelligence of the optimizer, this may lead to two scans of the table, but I think yours has the same problems going for it - that subquery is going to scan the whole table I think.

Petr Kozelek Over a year ago

Clean idea. Thumb up! Maybe it can have performance problems but I think other solutions would have bigger troubles with that, too.

Tobiasopdenbrouw Over a year ago

Thanks for both the answer and the clarification comment.

Lieven Keersmaekers · Accepted Answer · 2010-08-26 09:06:29Z

This is one way to do it using pretty much any SQL flavor.

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 a 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
        )

Yves M. · Accepted Answer · 2010-08-26 09:07:50Z

1

Sorry was missundertanding your question on the first try...

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

edited Aug 26, 2010 at 9:07

answered Aug 26, 2010 at 8:51

Yves M.

3,31816 silver badges12 bronze badges

3 Comments

Tobiasopdenbrouw Over a year ago

Are you ignoring that 'Mary Bush' should be grouped with 'Bush Mary'?

Mihai Om Over a year ago

No. the first_name and last_name attributes need to louse their logic. In the final answer there must be a John Smith record for both John Smith and Smith John initial records.

Yves M. Over a year ago

Sorry my fault. Missunderstood the question completely.

APC · Accepted Answer · 2010-08-26 09:04:47Z

Here's a solution which uses Oracle functions. Other flavours of SQL 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>

Rohit · Accepted Answer · 2010-08-26 09:23:17Z

0

I think this might work in MS-SQL

select * from People

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

answered Aug 26, 2010 at 9:23

Rohit

5,7314 gold badges35 silver badges61 bronze badges

Comments

Rodolpho Brock · Accepted Answer · 2012-08-17 17:50:57Z

0

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

answered Aug 17, 2012 at 17:50

Rodolpho Brock

8,1652 gold badges30 silver badges27 bronze badges

Collectives™ on Stack Overflow

SQL SELECT distinct rows from a table by multiple columns ignoring columns order (significance)

6 Answers 6

4 Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related