How to select distinct value from one MySQL column only?

MySQLMySQLi Database

To select distinct value from one column only, you can use aggregate function MAX() along with GROUP BY. Let us first create a table −

mysql> create table distinctFromOneColumn
   -> (
   -> StudentId int,
   -> StudentName varchar(100)
   -> );
Query OK, 0 rows affected (0.77 sec)

Following is the query to insert records in the table using insert command −

mysql> insert into distinctFromOneColumn values(1001,'John');
Query OK, 1 row affected (0.15 sec)

mysql> insert into distinctFromOneColumn values(1002,'Carol');
Query OK, 1 row affected (0.15 sec)

mysql> insert into distinctFromOneColumn values(1001,'Sam');
Query OK, 1 row affected (0.15 sec)

mysql> insert into distinctFromOneColumn values(1001,'David');
Query OK, 1 row affected (0.16 sec)

mysql> insert into distinctFromOneColumn values(1002,'Ramit');
Query OK, 1 row affected (0.15 sec)

mysql> insert into distinctFromOneColumn values(1003,'Bob');
Query OK, 1 row affected (0.21 sec)

Following is the query to display all records from the table using select statement −

mysql> select * from distinctFromOneColumn;

This will produce the following output −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1001      | John        |
| 1002      | Carol       |
| 1001      | Sam         |
| 1001      | David       |
| 1002      | Ramit       |
| 1003      | Bob         |
+-----------+-------------+
6 rows in set (0.00 sec)

Here is the query to select a distinct value from one column only −

mysql> select StudentId,MAX(StudentName) AS StudentName
   -> from distinctFromOneColumn
   -> group by StudentId;

This will produce the following output −

+-----------+-------------+
| StudentId | StudentName |
+-----------+-------------+
| 1001      | Sam         |
| 1002      | Ramit       |
| 1003      | Bob         |
+-----------+-------------+
3 rows in set (0.00 sec)
raja
Published on 16-Apr-2019 17:03:32
Advertisements