MYSQL select DISTINCT values from two columns?
To select distinct values in two columns, you can use least() and greatest() function from MySQL.
Let us create a table with two columns −
mysql> create table SelectDistinctTwoColumns
−> (
−> StudentId int,
−> EmployeeId int
−> );
Query OK, 0 rows affected (0.60 sec)
Now you can insert records in the table. The query to insert records is as follows −
mysql> insert into SelectDistinctTwoColumns values(100,101);
Query OK, 1 row affected (0.39 sec)
mysql> insert into SelectDistinctTwoColumns values(102,103);
Query OK, 1 row affected (0.13 sec)
mysql> insert into SelectDistinctTwoColumns values(104,105);
Query OK, 1 row affected (0.18 sec)
mysql> insert into SelectDistinctTwoColumns values(100,101);
Query OK, 1 row affected (0.14 sec)
mysql> insert into SelectDistinctTwoColumns values(102,103);
Query OK, 1 row affected (0.12 sec)
mysql> insert into SelectDistinctTwoColumns values(106,107);
Query OK, 1 row affected (0.36 sec)
mysql> insert into SelectDistinctTwoColumns values(104,105);
Query OK, 1 row affected (0.17 sec)
mysql> insert into SelectDistinctTwoColumns values(105,104);
Query OK, 1 row affected (0.35 sec)
Display all records from table with the help of select statement. The query is as follows −
mysql> select *from SelectDistinctTwoColumns;
The following is the output −
+-----------+------------+
| StudentId | EmployeeId |
+-----------+------------+
| 100 | 101 |
| 102 | 103 |
| 104 | 105 |
| 100 | 101 |
| 102 | 103 |
| 106 | 107 |
| 104 | 105 |
| 105 | 104 |
+-----------+------------+
8 rows in set (0.00 sec)
Look at the above output. Some duplicate value in both the columns can be seen. Here is the query that selects distinct value from columns −
mysql> select distinct least(StudentId, EmployeeId) as FirstColumn,
−> greatest(StudentId, EmployeeId) as SecondColumn from SelectDistinctTwoColumns;
The following is the output −
+-------------+--------------+
| FirstColumn | SecondColumn |
+-------------+--------------+
| 100 | 101 |
| 102 | 103 |
| 104 | 105 |
| 106 | 107 |
+-------------+--------------+
4 rows in set (0.00 sec)
Published on 11-Jan-2019 14:16:25
