CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Inversus
Inversus

Reputation: 3175

SQL - Search a table for all instances where a value is repeated

I'm looking to find a way to search a table for duplicate values and return those duplicates (or even just one of the set of duplicates) as the result set.

For instance, let's say I have these data:

uid    |   semi-unique id
1      |       12345
2      |       21345
3      |       54321
4      |       41235
5      |       12345
6      |       21345

I need to return either:

12345
12345
21345
21345

Or:

12345
21345

I've tried googling around and keep coming up short. Any help please?

Upvotes: 1

Views: 220

Answers (3)

Josh
Josh

Reputation: 1395

Sorry, I was short on time earlier so I couldn't explain my answer. The first query groups the semi_unique_ids that are the same and only returns the ones that have a duplicate.

SELECT semi_unique_id
FROM your_table
GROUP BY semi_unique_id
HAVING COUNT(semi_unique_id) > 1

If you wanted to get the uid in the query too you can easily add it like so.

SELECT uid,
       semi_unique_uid
FROM   your_table
GROUP BY
       semi_unique_id,
       uid
HAVING COUNT(semi_unique_id) > 1

Lastly if you would like to get an idea of how many duplicates per row returned you would do the following.

SELECT uid,
       semi_unique_uid,
       COUNT(semi_unique_uid) AS unique_id_count
FROM   your_table
GROUP BY
       semi_unique_id,
       uid
HAVING COUNT(semi_unique_id) > 1

Upvotes: 3

Sandip Bantawa
Sandip Bantawa

Reputation: 2880

SELECT t.semi_unique_id AS i
FROM   TABLE t
GROUP BY
       t.semi_unique_id
HAVING (COUNT(t.semi_unique_id) > 1)

Try this for sql-server

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

To get each row, you can use window functions:

select t.*
from (select t.*, count(*) over (partition by [semi-unique id]) as totcnt
      from t
     ) t
where totcnt > 1

To get just one instance, try this:

select t.*
from (select t.*, count(*) over (partition by [semi-unique id]) as totcnt,
             row_number() over (partition by [semi-unique id] order by (select NULL)
                               ) as seqnum
      from t
     ) t
where totcnt > 1 and seqnum = 1

The advantage of this approach is that you get all the columns, instead of just the id (if that helps).

Upvotes: 3

Related Questions