MySQL remove duplicate select in DISTINCT keyword usage

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, and can not return other fields, this problem has plagued me for a long time, with distinct can not solve the words, I only use a double-loop query to solve, and so for a very large number of stations, will undoubtedly directly affect the efficiency. So I spent a lot of time to study the problem, the online can not find the solution, during the Jongjong pull to help, the result is that both of us are depressed ...

Let's take a look at the example below:

Table
ID Name
1 A
2 b
3 C
4 C
5 b

The library structure is probably like this, this is just a simple example, the actual situation will be much more complex.

For example, if I want to use a single statement to find all the data that name does not repeat, then you must use distinct to remove the redundant duplicate records.

Select DISTINCT name from table
The resulting results are:

Name
A
B
C

It seems to be working, but what I want to get is the ID value? Change the query statement:

Select DISTINCT name, ID from table

The result would be:

ID Name
1 A
2 b
3 C
4 C
5 b

Why doesn't distinct work? The role is up, but he also functions two fields, that is, the ID must be the same as the name will be excluded ....

Let's change the query statement:

Select ID, distinct name from table

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 ....

It's a lot of trouble, huh? Indeed, no effort has been able to solve the problem. No way, continue to ask someone.

Holding a Java programmer in the company, he showed me that after using distinct in Oracle, I didn't find a solution to MySQL, and he suggested that I try group by before work ends.

Tried for a while, also not, finally found a usage in MySQL manual, with group_concat (distinct name) with the group by name to achieve the function I need, excited, God bless me also, quickly try.

Error............ Depressed....... Even the MySQL handbook with me, first gave me hope, and then pushed me to disappointment, good ruthless ....

After a closer look, the Group_concat function is 4.1 supported, Halo, I am 4.0. No way, upgrade, finish a test, success ...

Finally, but then, you have to ask customers to upgrade.

Suddenly the spirit flashes, since you can use the Group_concat function, can the other functions be OK?

Hurriedly with count function a try, success, I .... Want to cry Ah, took so much work ... The original is so simple ...

Now release the full statement:

SELECT *, COUNT (distinct name) from the table group by name

Results:

ID Name count (distinct name)
1 a 1
2 B 1
3 C 1

The last item is superfluous, do not care on the line, the purpose to achieve ...

Alas, the original MySQL so stupid, gently put him to cheat over, depressed also on me (yes, and let the guy), now take out hope you don't be this problem toss.

Oh, yes, and by the way, group by must be placed before order by and limit, otherwise it will error ...! Okay, OK.