Select unique combinations of two field values

Question

This may have been asked before, but I cannot find the answer.

The table data has two columns:

Source Dest 1 2 1 2 2 1 3 1 

I am trying to find an MS Access 2003 SQL query that will be returned:

 1 2 3 1 

But all to no avail. Please help!

UPDATE: exactly, I'm trying to exclude 2.1, because 1.2 is already included. I only need unique combinations where the sequence does not matter.

Answer 1

To access Ms you can try

 SELECT DISTINCT * FROM Table1 tM WHERE NOT EXISTS(SELECT 1 FROM Table1 t WHERE tM.Source = t.Dest AND tM.Dest = t.Source AND tm.Source > t.Source) 

EDIT:

Table example Data that are the same ...

 SELECT DISTINCT * FROM Data tM WHERE NOT EXISTS(SELECT 1 FROM Data t WHERE tM.Source = t.Dest AND tM.Dest = t.Source AND tm.Source > t.Source) 

or (Nice and Access Formatted ...)

 SELECT DISTINCT * FROM Data AS tM WHERE (((Exists (SELECT 1 FROM Data t WHERE tM.Source = t.Dest AND tM.Dest = t.Source AND tm.Source > t.Source))=False)); 

Answer 2

Use this query:

 SELECT distinct * from tabval ; 

Answer 3

To eliminate duplicates, "picking the perfect one" is easier than "group by":

 select distinct source,dest from data; 

EDIT: Now I see that you are trying to get unique combinations (not including both 1.2 and 2.1). You can do it like:

 select distinct source,dest from data minus select dest,source from data where source < dest 

“minus” flips the order around and excludes cases when you already have a match; "where the source <dest" does not allow to remove both (1,2) and (2,1)

Answer 4

Your question is not asked correctly. "Unique Combinations" are all your notes. but I think you mean one line per source. so it is:

 SELECT * FROM tab t1 WHERE t1.Dest IN ( SELECT TOP 1 DISTINCT t2.Dest FROM tab t2 WHERE t1.Source = t2.Source ) 

Answer 5

 SELECT t1.* FROM (SELECT LEAST(Source, Dest) AS min_val, GREATEST(Source, Dest) AS max_val FROM table_name) AS t1 GROUP BY t1.min_val, t1.max_val 

Will return

 1, 2 1, 3 

in MySQL.