-1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amelia
  • 159
  • 1
  • 3
  • 17

3 Answers3

0

Assuming id is a primary key

select *
from myTable t1
where exists (select 1 
                 from myTable t2
                 where t2.text = t1.text and t2.id != t1.id)
Serg
  • 22,285
  • 5
  • 21
  • 48
0

You can use similar to following query:

SELECT
  column1, COUNT(*)
FROM table
GROUP BY column1
HAVING COUNT(*) > 1
Imran Rizvi
  • 7,331
  • 11
  • 57
  • 101
0

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
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
  • Thanks. This is getting result what I need. but a bit space issue. sample data in text column have values TextColumn : AsdaSTORES, AsdaSTORES , Dynamic Motor, Dynamic Motor . when there is space at last of the word the rows are not matching as duplicates. I think I need to add RTRIM or LTRIM to the column. please suggest – Amelia Sep 01 '16 at 10:55
  • if you wanted to remove the space from your text data,use REPLACE function..use the updated code.. – Unnikrishnan R Sep 01 '16 at 10:58