MySQL select * with distinct id?

You can use GROUP BY command for select with distinct id. The syntax is as follows −

SELECT *FROM yourTableName GROUP BY yourColumnName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table DistinctIdDemo
   -> (
   -> Id int,
   -> Name varchar(20),
   -> Age int
   -> );
Query OK, 0 rows affected (1.03 sec)

Insert some records in the table using insert command. Here, we have added ID with duplicate values.

The query is as follows −

mysql> insert into DistinctIdDemo values(1,'Mike',23);
Query OK, 1 row affected (0.16 sec)

mysql> insert into DistinctIdDemo values(2,'Sam',24);
Query OK, 1 row affected (0.20 sec)

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

mysql> insert into DistinctIdDemo values(1,'John',28);
Query OK, 1 row affected (0.33 sec)

mysql> insert into DistinctIdDemo values(3,'David',26);
Query OK, 1 row affected (0.22 sec)

mysql> insert into DistinctIdDemo values(2,'Larry',29);
Query OK, 1 row affected (0.20 sec)

Let us now display records −

mysql> select *from DistinctIdDemo;

The following is the output −

+------+-------+------+
| Id   | Name  | Age  |
+------+-------+------+
| 1    | Mike  | 23   |
| 2    | Sam   | 24   |
| 1    | Carol | 23   |
| 1    | John  | 28   |
| 3    | David | 26   |
| 2    | Larry | 29   |
+------+-------+------+
6 rows in set (0.00 sec)

Here is the query to get all records from the table with distinct id −

mysql> select *from DistinctIdDemo group by Id;

The following is the output −

+------+-------+------+
| Id   | Name  | Age  |
+------+-------+------+
| 1    | Mike  | 23   |
| 2    | Sam   | 24   |
| 3    | David | 26   |
+------+-------+------+
3 rows in set (0.00 sec)