Using distinct to query multiple records in Mysql _mysql

When using MySQL, there are times when you need to query for records that are not duplicated in a field, although MySQL provides a distinct keyword to filter out excess duplicates, but it often only uses it to return the number of records that are not duplicates, rather than using it to return all values that are not logged. The reason for this is that distinct can only return its target field. And can not return to other fields, this problem let me worry for a long time, with distinct can not be resolved, I only use the double loop query to solve, and so for a very large amount of data station, will undoubtedly directly affect the efficiency. So I spent a lot of time to study this problem, online also can not find solutions, during the Jongjong pull to help, the result is that we both are depressed ...

Let's take a look at the following examples:

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

For example, I want to use a statement query to get the name does not duplicate all the data, it must use distinct to remove unnecessary duplicate records.

Select DISTINCT name from table
The results obtained are:

Name
A

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 will be:

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

Why didn't distinct play a role? The effect is up, but he also works on two fields, that is, the ID must be the same as name will be excluded ...

We will change the query statement:

Select ID, distinct name from table

Unfortunately, you can't get anything except the wrong message, distinct must be placed at the beginning. Is it hard to put distinct in the where? Can, still error ....

Isn't that a problem? Indeed, the effort has failed to solve the problem. No way, keep looking for someone to ask.

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

Try for a long time, also not, finally in the MySQL manual to find a usage, with GROUP_CONCAT (distinct name) with group by name to achieve the function I need, excited, God save me also, quickly try.

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

Again careful check, Group_concat function is 4.1 support, Halo, I 4.0. No way, upgrade, finish level a try, success ...

It's finally done, but it's going to require a client upgrade.

A sudden flash of a mind, since the GROUP_CONCAT function can be used, that other functions can do?

Use the Count function to try, success, I ... Want to cry ah, so much time. It was so simple ...

Now release the full statement:

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

Results:

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

The last one is superfluous, do not need to control on the line, to achieve ...

Alas, the original MySQL so stupid, gently put him fooled past, depressed also on me (right, there are also allow that fellow), now take out hope that we do not be the problem toss.

Oh, yes, and by the way, group by must be placed in order by and limit before, otherwise it will be an error, almost, to the capacity to put the site up, I continue to busy ...

-----------------------------------------------------------------------------------------


The more depressing thing happened, the Jongjong found in preparation for the submission that there was a simpler solution ...

Select ID, name from table group by name

It seems that the knowledge of MySQL is too superficial, not afraid of being jokes, issued to let everyone do not make the same mistake ...

by Sol