select those fields that has more than one entry

Question

i don't get it of how to write a simple sql statement that shows only those entries that appers more than once.

e.x.

NodeID  Number

0       12
1       12
2       12
0       13
1       13
0       14
1       15
2       16
1       17

Select nodeID, number from Table where number appearing more than once with different nodeID

Result:

NodeID  Number
0       12
1       12
2       12
0       13
1       13

score 3 · Answer 1 · answered Apr 20 '13 at 11:14

3

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  Number
            FROM    TableName
            GROUP   BY Number
            HAVING  COUNT(*) > 1
        ) b ON a.Number = b.Number

SQLFiddle Demo

Another way is to use EXISTS

SELECT  a.*
FROM    TableName a
WHERE   EXISTS
        (
            SELECT  1
            FROM    TableName b
            WHERE   a.Number = b.Number
            GROUP   BY Number
            HAVING  COUNT(*) > 1
        )

SQLFiddle Demo

answered Apr 20 '13 at 11:14

John Woo

258,903
69
498
492

This selects records even if `12` is appearing with repeating `nodeID` http://www.sqlfiddle.com/#!2/97d68/1/0 . Think I'm misunderstanding the question? – Ejaz Apr 20 '13 at 11:18
@Ejay the answer is based on the example above. You have a point about duplicates but it can be avoided if the OP has unique constraint for every combination. – John Woo Apr 20 '13 at 11:26
@Ejay here's the solution for you example, http://www.sqlfiddle.com/#!2/97d68/4 – John Woo Apr 20 '13 at 11:28
1

that's great :) Your solution indeed solved OP's problem. I was just was curious about duplicates case – Ejaz Apr 20 '13 at 11:30

score 0 · Answer 2 · answered Apr 20 '13 at 11:17

0

Try this

SELECT NodeID , Number FROM TableName WHERE Number IN
(SELECT Number FROM TableName  GROUP BY Number HAVING  COUNT(*) > 1)

answered Apr 20 '13 at 11:17

bvr

4,786
1
20
24

score 0 · Answer 3 · answered Apr 20 '13 at 11:18

0

You could also use EXISTS without GROUP BY:

SELECT nodeid, 
       number 
FROM   Table1 t1 
WHERE  EXISTS(SELECT 1 
              FROM   Table1 t2 
              WHERE  t1.number = t2.number 
                     AND t1.nodeid <> t2.nodeid)

DEMO

answered Apr 20 '13 at 11:18

Tim Schmelter

450,073
74
686
939

didierc · Answer 4 · 2013-04-20T11:26:28.083

My first idea was to do a self join like below, but it won't work because the grouping happens afterwards.

SELECT T1.NodeId, T2.Number, COUNT(T2.Number) C FROM Table T1 INNER JOIN Table T2 USING(Number) WHERE C > 1 GROUP BY T2.Number

Hence you must do the grouping select first, then make the join:

SELECT NodeId, Number
FROM Table T1
INNER JOIN (
     SELECT Number, COUNT(Number) C
     FROM Table
    WHERE C > 1
    GROUP BY Number
) T2 USING(Number)

select those fields that has more than one entry

4 Answers4