Fetch unique combinations of two field values

Question

Probably it has been asked before but I cannot find an answer.

Table Data has two columns:

Source   Dest
1         2
1         2
2         1
3         1

I trying to come up with a MS Access 2003 SQL query that will return:

1       2
3       1

But all to no avail. Please help!

UPDATE: exactly, I'm trying to exclude 2,1 because 1,2 already included. I need only unique combinations where sequence doesn't matter.

Answer 1

Use this query :

SELECT distinct *  from tabval ;

Answer 2

To eliminate duplicates, "select distinct" is easier than "group by":

select distinct source,dest from data;

EDIT: I see now that you're trying to get unique combinations (don't include both 1,2 and 2,1). You can do that like:

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

The "minus" flips the order around and eliminates cases where you already have a match; the "where source < dest" keeps you from removing both (1,2) and (2,1)

Answer 3

your question is asked incorrectly. "unique combinations" are all of your records. but i think you mean one line per each 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 4

For Ms Access 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:

Example with table Data, which is 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 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.