0

I am trying to develope a query to fetch the rows having duplicate values, i need to fetch both records i.e duplicating record and the real one, for example

table

id  keyword
-------------------
1   Apple
2   Orange
3   Apple
4   Grape
5   Banana
6   Grape

The query result should be:

id  keyword
-------------------
1   Apple
3   Apple
4   Grape
6   Grape

Please anyone help me!

Roger Rowland
  • 25,885
  • 11
  • 72
  • 113
Saritha
  • 1,947
  • 5
  • 18
  • 24

3 Answers3

2

Query:

select * from 
table where keyword in
(select keyword 
from table
group by keyword 
having count(keyword)>1)
Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
0

This might help: 

SELECT t1.id, t1.keyword                                     
FROM table t1              
INNER JOIN table t2                   
ON t1.id != t2.id           
AND t1.keyword=t2.keyword

Tested on SQL Fiddle http://sqlfiddle.com/#!2/44dbb/1/0

ritesh
  • 907
  • 3
  • 11
  • 31
  • Thanks for the reply! But i am fetching records from only one table, not two tables, so i think this INNER JOIN is not needed. – Saritha May 20 '13 at 07:22
  • Please check this : [MySQL “IN” queries terribly slow with subquery but fast with explicit values](http://stackoverflow.com/questions/5018284/mysql-in-queries-terribly-slow-with-subquery-but-fast-with-explicit-values) – ritesh May 20 '13 at 07:59
0

One way to do it:

SELECT * 
FROM `table` t1
WHERE 
    (SELECT COUNT(*) FROM `table` t2 WHERE t2.keyword = t1.keyword) > 1

And another way:

SELECT t1.*
FROM `table` t1
JOIN `table` t2 ON t1.keyword = t2.keyword
WHERE t1.id != t2.id
Jan.J
  • 3,050
  • 1
  • 23
  • 33