CODEWITHSUNDEEP
sqlsql-server
user1464139
user1464139

Reputation:

How can I find duplicate values in the column of a table I am trying to index?

I have this DDL:

CREATE TABLE [dbo].[FreqLeeds] (
    [Id]   INT             NOT NULL,
    [Freq] DECIMAL (18, 5) NOT NULL,
    [Text] NVARCHAR (50)   NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

After populating the table I tried to create an index:

CREATE UNIQUE INDEX [IX_FreqLeeds_Text] ON [dbo].[FreqLeeds] ([Text])

But the create failed because of a duplicate value.

How can I determine which rows have duplicates and what the value is ?

Upvotes: 0

Views: 30

Answers (2)

o0mardok0o
o0mardok0o

Reputation: 21

you can try to query your table in this way 

Select count(*), [Text]
from [dbo].[FreqLeeds]
group by [Text]
having count(*) > 1

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39487

You can find the text which are present more than once using aggregation:

select [Text]
from [dbo].[FreqLeeds]
group by [Text]
having count(*) > 1

If you want to see all the rows (with all the columns) for the text that are present more than once, you can use window function count in subquery (or CTE) and filter:

select *
from (
    select 
        t.*,
        count(*) over (partition by [Text]) cnt
    from [dbo].[FreqLeeds] t
) t where cnt > 1;

Upvotes: 1

Related Questions