CODEWITHSUNDEEP
sqlms-access
SQL n00b
SQL n00b

Reputation: 53

Fetch unique combinations of two field values

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.

Upvotes: 5

Views: 3401

Answers (5)

Adriaan Stander
Adriaan Stander

Reputation: 166396

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

Upvotes: 2

David Gelhar
David Gelhar

Reputation: 27900

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)

Upvotes: 0

True Soft
True Soft

Reputation: 8796

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.

Upvotes: 0

Andrey
Andrey

Reputation: 60065

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
)

Upvotes: 0

Pavunkumar
Pavunkumar

Reputation: 5335

Use this query : 

SELECT distinct *  from tabval ;

Upvotes: -1

Related Questions