Find rows that have the same value on a column in MySQL?

First, we will create a table and insert some values into the table. Let us create a table.

mysql> create table RowValueDemo
   -> (
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.69 sec)

Insert records using the insert command. We have added duplicate values as well for our example.

mysql> insert into RowValueDemo values('John');
Query OK, 1 row affected (0.14 sec)

mysql> insert into RowValueDemo values('Bob');
Query OK, 1 row affected (0.14 sec)

mysql> insert into RowValueDemo values('Carol');
Query OK, 1 row affected (0.11 sec)

mysql> insert into RowValueDemo values('John');
Query OK, 1 row affected (0.24 sec)

mysql> insert into RowValueDemo values('John');
Query OK, 1 row affected (0.09 sec)

mysql> insert into RowValueDemo values('John');
Query OK, 1 row affected (0.10 sec)

mysql> insert into RowValueDemo values('Bob');
Query OK, 1 row affected (0.09 sec)

mysql> insert into RowValueDemo values('Bob');
Query OK, 1 row affected (0.18 sec)

Displaying all records with the help of select statement.

mysql> select *from RowValueDemo;

Here is the output.

+-------+
| Name  |
+-------+
| John  |
| Bob   |
| Carol |
| John  |
| John  |
| John  |
| Bob   |
| Bob   |
+-------+
8 rows in set (0.00 sec)

Now, we ill get only the rows that have same value for column using the following query −

mysql> select * from RowValueDemo where Name in (
   ->  select Name from RowValueDemo
   ->  group by Name having count(*) > 1
   -> );

The following is the output.

+------+
| Name |
+------+
| John |
| Bob  |
| John |
| John |
| John |
| Bob  |
| Bob  |
+------+
7 rows in set (0.06 sec)

Arjun Thakur

Published on 29-Nov-2018 11:02:08

• Related Questions & Answers
• How to return rows that have the same column values in MySQL?
• Count how many rows have the same value in MySQL?
• Get rows that have common value from the same table with different id in MySQL
• MySQL Select Rows where two columns do not have the same value?
• Python - Print rows from the matrix that have same element at a given index
• Exclude rows based on column value when another duplicate column value is found in MySQL?
• Find rows where column value ends with a specific substring in MySQL?
• Count the same value of each row in a MySQL column?
• Find the Maximum Value in a Column in MySQL
• MySQL query to find sum of fields with same column value?
• Finding the sum of integers from multiple MySQL rows in same column?
• Group MySQL rows in an array by column value?
• MySQL query to find same value on multiple fields with LIKE operator?
• MySQL query to find the average of rows with the same ID
• How to remove a column from a data frame that contains same value in R?