CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mtallon
mtallon

Reputation: 135

Select rows that do NOT have a DISTINCT value

Can someone suggest a method for selecting rows that do not have a unique value for a column?

If I have five records, with 1 record that has CustNo = 7, 1 record that has CustNo = 9, and three records that have CustNo = 11, I only want to select the three rows that have CustNo = 11

Upvotes: 1

Views: 158

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you need to get the complete rows for rows that are duplicates, then use window functions:

select t.*
from (select t.*, count(*) over (partition by CustNo) as cnt
      from table t
     ) t
where cnt > 1;

Upvotes: 0

T.S.
T.S.

Reputation: 19340

This will find records, and count of them, that have duplicates in CustNo

Select CustNo, Count(*) From Table1
Group By CustNo
Having Count(*) > 1

And another way for full solution (get full record of only those that have duplicates), using group and join

Select t1.*
From Table1  t1 inner join 
    (Select CustNo cn From Table1
     Group By CustNo
     Having Count(*) > 1) t2 On t1.CustNo = t2.cn

Upvotes: 4

Karthik Ganesan
Karthik Ganesan

Reputation: 4222

to display all the rows which are not distinct you need to have a sub query like this

select * from CustDetails
WHERE CustNo IN (SELECT CustNo from CustDetails 
                 group by CustNo 
                 having count(CustNo) > 1)

here is the sql fiddle for the same

http://sqlfiddle.com/#!3/1c9a4/4

Upvotes: 4

Aaron
Aaron

Reputation: 57748

Just doing a SELECT/GROUP BY will return only one row for the records where CustNo is non-unique. Implementing that in a sub-query however, should do it.

If I'm understanding your question correctly, then this will return the rows in CustomerTable where CustNo is not unique:

SELECT *
FROM CustomerTable
WHERE CustNo IN (
    SELECT CustNo
    FROM CustomerTable
    GROUP BY CustNo
    HAVING COUNT(*) > 1)

Upvotes: 2

Fabian Bigler
Fabian Bigler

Reputation: 10895

This retrieves all duplicate records:

SELECT CustNo FROM MyTable Group By CustNo Having COUNT(*) > 1

Upvotes: 3

Related Questions