Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Check a column for unique value in MySQL
You can use subquery for this. Let us first create a demo table
mysql> create table uniqueBothColumnValueSameDemo -> ( -> UserId int, -> UserValue int -> ); Query OK, 0 rows affected (0.64 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into uniqueBothColumnValueSameDemo values(10,20); Query OK, 1 row affected (0.21 sec) mysql> insert into uniqueBothColumnValueSameDemo values(10,20); Query OK, 1 row affected (0.09 sec) mysql> insert into uniqueBothColumnValueSameDemo values(20,30); Query OK, 1 row affected (0.10 sec) mysql> insert into uniqueBothColumnValueSameDemo values(20,40); Query OK, 1 row affected (0.11 sec) mysql> insert into uniqueBothColumnValueSameDemo values(50,10); Query OK, 1 row affected (0.10 sec) mysql> insert into uniqueBothColumnValueSameDemo values(50,10); Query OK, 1 row affected (0.07 sec) mysql> insert into uniqueBothColumnValueSameDemo values(60,30); Query OK, 1 row affected (0.07 sec) mysql> insert into uniqueBothColumnValueSameDemo values(60,30); Query OK, 1 row affected (0.08 sec) mysql> insert into uniqueBothColumnValueSameDemo values(60,50); Query OK, 1 row affected (0.12 sec)
Display all records from the table using select statement. The query is as follows −
mysql> select *from uniqueBothColumnValueSameDemo;
The output is as follows
+--------+-----------+ | UserId | UserValue | +--------+-----------+ | 10 | 20 | | 10 | 20 | | 20 | 30 | | 20 | 40 | | 50 | 10 | | 50 | 10 | | 60 | 30 | | 60 | 30 | | 60 | 50 | +--------+-----------+ 9 rows in set (0.00 sec)
Here is the query to check column for unique value
mysql> SELECT DISTINCT UserId, UserValue -> FROM uniqueBothColumnValueSameDemo tbl1 -> WHERE (SELECT count(DISTINCT UserValue) -> FROM uniqueBothColumnValueSameDemo tbl2 -> WHERE tbl2.UserId = tbl1.UserId) = 1;
The output is as follows
+--------+-----------+ | UserId | UserValue | +--------+-----------+ | 10 | 20 | | 50 | 10 | +--------+-----------+ 2 rows in set (0.00 sec)
Updated on: 30-Jul-2019
309 Views
Advertisements