I want find duplicate data? and display duplicate records only?

Question

in my table having data like this

+-----+----------+  
| sno | name     |  
+-----+----------+  
| 101 | Raju     |  
| 102 | Raju     |  
| 103 | Santhosh |  
| 104 | Santhosh |  
| 105 | madhavi  |  
| 106 | suheel   |  
+-----+----------+  

in that i want find dupliacte records and display sno(number) only
for example output should be like this

+-----+  
| sno |  
+-----+  
| 101 |  
| 102 |  
| 103 |  
| 104 |  
+-----+  

Answer 1

In a Derived table, get all the name values which have duplicates. To do that, we can GROUP BY on name and use HAVING to consider only those names, where COUNT(*) (total number of rows for that name) is more than 1.

Now, we can join back to the main table to get their respective sno values

SELECT
 t.sno
FROM your_table t
JOIN (SELECT name 
      FROM your_table 
      GROUP BY name
      HAVING COUNT(*) > 1) dt
  ON dt.name = t.name 

Answer 2

Here is a MySQL 8+ way of doing this:

SELECT sno
FROM
(
    SELECT t.*, COUNT(*) OVER (PARTITION BY name) cnt
    FROM yourTable t
) t
WHERE cnt > 1;

Answer 3

You can try using correlated subquery

select sno from tablename a 
where name in (select 1 from tablename b where a.name=b.name having count(name)>1 )