CODEWITHSUNDEEP
mysql
user99999
user99999

Reputation: 2024

Select unique values for each ID

I have the following table:

id     | hash
1        aaa
1        bbb
1        ccc
2        ddd
2        eee
2        aaa
3        bbb

And I want to get unique hashes for provided ID, for example for ID 1 I would get here 1 as a result, because aaa and bbb already exist in different IDs. For 2 I'd get 2, beucase only aaa exists elsewhere, etc.

Desired result:

id | result
 1      1
 2      2

How can I do that?

EDIT:

I have tried:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.hash = t2.hash
WHERE t1.id = 1 AND t2.id != 1;

But it doesn't return the correct results, but rather hashes that exist in 1 ID, and one of the others.

Upvotes: 2

Views: 77

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Get unique hashes with a having clause, then count hashes per id:

select id, count(*)
from
(
  select hash, id
  from mytable
  group by hash
  having count(*) = 1
) one_time_hashes
group by id;

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

SELECT x.id
     , COUNT(*) 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.id <> x.id 
   AND y.hash = x.hash 
 WHERE y.id IS NULL 
 GROUP 
    BY id;

A NOT EXISTS type solution may well prove faster.

Upvotes: 0

kostas
kostas

Reputation: 461

select ID,COUNT(hash) as Expr1
from table1 
where hash not in 
  (
     select hash 
     from 
     table1 
     group by hash 
     having count(ID)>1
   ) 
 group by ID

Upvotes: 1

Christian COMMARMOND
Christian COMMARMOND

Reputation: 25

Your question is : And I want to get unique hashes for provided ID, for example for ID 1 I would get here 1 as a result, because aaa and bbb already exist in different IDs. For 2 I'd get 2, beucase only aaa exists elsewhere, etc. For 1, return 1, For 2, return 2... Are you sure that you explained correctly what you want?

I suppose you want to return ccc for id=1, because this hash is unique, ddd or eee for 2...

If so, this example which should work (I am on Oracle):

select hash
from table t1
where ID = 1
and hash not in (select hash 
               from table t2
              where t2.hash = t1.hash
                and T2.ID   <> t1.id)
and rownum=1 
;

Take care, you need some indexes for performance.

Hopes this helps.

Christian

Upvotes: 0

Related Questions