How to use SELECT distinct in MySQL

When using MySQL, it is sometimes necessary to query a field that does not repeatedly record, although MySQL provides distinct this keyword to filter out redundant repeated records only to keep one, but often only use it to return the number of records not repeatedly, instead of using it to return the full value of not re-recorded. The reason for this is that distinct can only return its target field and cannot return other fields. Experiments have been implemented, for example, in the following ways.

The proportions are as follows:
This is the structure of the test table.
ID test1 test2
1 a 1
2 a 2
3 a 3
4 a 1
5 B 1
6 B 2
7 B 3
8 B 2
For example, I want to use a statement query to get test1 do not repeat all the data, it must be used distinct eliminate redundant repeated records.

Select distinct test1 from test
The resulting results are:
Test1
A
B
It seems to be working, but what I want to get is the ID value? Change the query statement:
SELECT DISTINCT test1, ID from test


Test1 ID
A 1
A 2
A 3
A 4
B 5
B 6
B 7
B 8
Why doesn't distinct work? The role is up, but he is the same time the role of two fields, that is, must be the same ID and test1 will be excluded. It's impossible. ID is self-growing.

。。

Let's change the query statement:

Select ID, distinct test1 from test
Very sorry. You can't get anything but the wrong information. Distinct must be placed at the beginning.

Is it difficult to put distinct in a where condition? Yes. Still error ...

。

。。。

By reviewing the manual. Can be achieved by Group_cancat:
SELECT ID, Group_concat (DISTINCT test1) from test group by Test1
ID Group_concat (DISTINCT test1)
2 B
5 b
But it only has after 4.1.0, for those old version of the database is not possible.


Can be implemented by other functions:
SELECT *, COUNT (distinct test1) from test group by Test1
ID test1 test2 count (distinct test1)
1 a 1 1
5 B 1 1
The last item is superfluous, no tube can be, the purpose of achieving ....

。

There are also simpler ways to achieve this:
Select ID, test1 from test group by Test1
ID test1
1 A
5 b

How to use SELECT distinct in MySQL