Show all result with more then 1 entry

Question

Suppose this is the result of a query

  id            value
------        ---------
  10              123
  10              422
  11              441
  11              986
  12              674
  13              648

What do i need to add to the query to return all the id's which have 2 or more values associated with them. So, in that case it will only return ID 10 & 11, but i need al the records.

so the result looks like:

 id            value
------        ---------
  10              123
  10              422
  11              441
  11              986

GROUP BY won't help me because i got only 1 back.. ;-)

Thank you.

Utkarsh Shekhar · Answer 1 · 2017-07-03T12:34:21.477

1

Here is a simple way to do this

select * from table where id in (select id from table group by id having count(id)>1) order by id;

edited Jul 03 '17 at 12:34

answered Jul 03 '17 at 12:13

Utkarsh Shekhar

139
1
8

Should be `IN`... – Pரதீப் Jul 03 '17 at 12:20
@Prdp Thank you , I forgot that "=" would cause errors when multiple rows are returned by the subquery. Corrected it likewise. – Utkarsh Shekhar Jul 03 '17 at 12:25
For some reason that `count(id)>=2` is not pleasing to my eyes. I would write it as `Count(id)>1` ;) – Pரதீப் Jul 03 '17 at 12:29
That certainly is a much more cleaner way, edited likewise. – Utkarsh Shekhar Jul 03 '17 at 12:33
You deserve +1 ;) – Pரதீப் Jul 03 '17 at 12:35

score 0 · Answer 2 · answered Jul 03 '17 at 12:04

0

You can use exists:

select t.*
from t
where exists (select 1 from t t2 where t2.id = t.id and t2.value <> t.value);

answered Jul 03 '17 at 12:04

Gordon Linoff

1,242,037
58
646
786

Show all result with more then 1 entry

2 Answers2