How to use SELECT distinct in MySQL

When using MySQL, it is sometimes necessary to query for a record that does not duplicate a field, although MySQL provides the DISTINCT keyword to filter out redundant duplicate records to keep only one, but it is often used only to return the number of distinct records, instead of using it to return all values that are not re-recorded. The reason is that distinct can only return its target field, but cannot return other fields, after experiment, it is possible to implement the following methods.

Examples 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, if I want to use a single statement to query for all the data that test1 does not repeat, then you must use distinct to remove the redundant duplicate records.
Select distinct test1 from test
The resulting results are:
Test1
A
B
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 also functions two fields, that is, must have the same ID and test1 will be excluded, this is not possible, the ID is automatically increased ...

Let's change the query statement:

Select ID, distinct test1 from test
Unfortunately, in addition to the error message you get nothing, distinct must be placed at the beginning. Is it difficult to put distinct in a where condition? Can, still error ....

The manual can be accessed through Group_cancat:
SELECT ID, Group_concat (DISTINCT test1) from test group by Test1
ID Group_concat (DISTINCT test1)
2 B
5 b
However, it can only be used after 4.1.0, for those older versions of the database is not.

Other functions can be implemented:
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, do not care on the line, the purpose to achieve ...

There are also simpler ways to do this:
Select ID, test1 from test group by Test1
ID test1
2 B
5 b

How to use SELECT distinct in MySQL