- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on 30-Jul-2019 22:30:23
- 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?
Advertisements