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
Select distinct combinations from two columns in MySQL?
To select distinct combinations from two columns, you can use CASE statement. Let us create a table with some columns.
The query to create a table is as follows −
mysql> create table select_DistinctTwoColumns -> ( -> Id int NOT NULL AUTO_INCREMENT, -> FirstValue char(1), -> SecondValue char(1), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.57 sec)
Now you can insert some records in the table using insert command. The query is as follows −
mysql> insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('s','t');
Query OK, 1 row affected (0.12 sec)
mysql> insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('t','u');
Query OK, 1 row affected (0.24 sec)
mysql> insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('u','v');
Query OK, 1 row affected (0.12 sec)
mysql> insert into select_DistinctTwoColumns(FirstValue,SecondValue) values('u','t');
Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement. The query is as follows −
mysql> select *from select_DistinctTwoColumns;
The following is the output −
+----+------------+-------------+ | Id | FirstValue | SecondValue | +----+------------+-------------+ | 1 | s | t | | 2 | t | u | | 3 | u | v | | 4 | u | t | +----+------------+-------------+ 4 rows in set (0.00 sec)
Here is the query to select distinct combinations from two column using case statement. The first column is ‘FirstValue’ and the second column name is ‘SecondValue’. The query is as follows −
mysql> SELECT distinct -> CASE -> WHEN FirstValue<SecondValue THEN FirstValue -> ELSE SecondValue -> END AS FirstColumn, -> CASE -> WHEN FirstValue > SecondValue THEN FirstValue -> ELSE SecondValue -> END AS SecondColumn -> FROM select_DistinctTwoColumns;
The following is the output:
+-------------+--------------+ | FirstColumn | SecondColumn | +-------------+--------------+ | s | t | | t | u | | u | v | +-------------+--------------+ 3 rows in set (0.00 sec)
Updated on: 30-Jun-2020
4K+ Views
Advertisements