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
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!
Query:
select * from
table where keyword in
(select keyword
from table
group by keyword
having count(keyword)>1)
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
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
