CODEWITHSUNDEEP
mysqlsql
Saritha
Saritha

Reputation: 1997

Fetch duplicate rows with SQL

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!

Upvotes: 0

Views: 1187

Answers (3)

ritesh
ritesh

Reputation: 917

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

Upvotes: 0

Jan.J
Jan.J

Reputation: 3080

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

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

Query:

select * from 
table where keyword in
(select keyword 
from table
group by keyword 
having count(keyword)>1)

Upvotes: 2

Related Questions