Amelia Amelia - 3 months ago 9
SQL Question

How to get duplicate text values from SQL query

I have to get table only with duplicate text values using SQL query. I have used

Having count(columnname) > 1
but I'm not getting result, only with duplicate values instead getting all values.

Can anyone suggest whether I have to add anything to my query?

Thanks.

Answer

Use the below query. mention the column which is getting duplicated in the patition by clause..

with CTE_1
AS
(SELECT *,COUNT(1) OVER(PARTITION BY LTRIM(RTRIM(REPLACE(yourDuplicateColumn,' ',''))) Order by -anycolunm- ) cnt
 FROM YourTable
 )
SELECT *
FROM CTE_1
WHERE cnt>1