Query multiple record values in MySQL with distinct

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 friend 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
b
c

It seems to be working, but what I want 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

Distinct why didn't it work? The effect was up, but he also played two fields, that is, the ID must be the same as the 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 difficult to put the distinct in the Where condition? can, likewise the error.

Is that a lot of trouble? 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, even the MySQL manual with me, first gave me hope, and then pushed me to disappointment.

Again, the Group_concat function is 4. 1 support, Halo, I'm 4. 0 of the. No way, upgrade, the level of 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?

Quickly use the Count function to try and succeed.

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, by the way, group by must be placed before the order by and limit, otherwise it will be an error.

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 laughed at, issued to let everyone do not make the same mistake.