Tips and usage of DISTINCT in select _ MySQL

The DISTINCT technique and usage in the select statement are occasionally displayed in a forum about the DISTINCT technique and usage. it is written by a moderator. I think it may be helpful to some people. so it is Reprinted. convenience

Below is the reprinted content

Unique query for a single table: distinct
Unique query for multiple tables: group
When using mysql, you sometimes need to query records with unique fields. Although mysql provides the keyword distinct to filter out redundant duplicate records, only one record is retained, but it is often used to return the number of records that do not repeat, instead of returning all values of records that do not repeat. The reason is that distinct can only return its Target Field, but cannot return other fields. if distinct cannot solve the problem, I only use double loop query to solve the problem, this will undoubtedly directly affect the efficiency of a station with a large data volume.
Let's take a look at the example below:
The table structure is as follows:
Id name
1
2 B
3 c
4 c
5 B
The structure of the basic table is like this. this is just a simple example. the actual multi-table query is much more complicated.
For example, if you want to use a statement to query all data with no duplicate names, you must use distinct to remove redundant duplicate records.
Select distinct name from table
The result is:
Name
A
B
C
It seems that the effect has been achieved, but what I want to get is the id value? Modify the query statement:
Select distinct name, id from table
The result is:
Id name
1
2 B
3 c
4 c
5 B
How does distinct not work? The role is actually effective, but it also serves two fields, that is, it must have the same id and name to be excluded.
Modify the query statement again:
Select id, distinct name from table
Unfortunately, you cannot get anything except the error message. you must start with distinct. Is it difficult to place distinct in the where condition? Try again and report an error.

I tried other methods that I could think of for a long time. I finally found a usage in the mysql Manual. I was excited to use group_concat (distinct name) and group by name to implement the functions I needed, you can try it now.
Error reported, depressing!
I can't even go through the mysql Manual. I gave me hope first, and then pushed me down.
Check again. The group_concat function is supported by 4.1, dizzy. I have 4.0. No way. Upgrade. the upgrade is successful.
Finally, the customer must be asked to upgrade.
Suddenly, the ghost machine flashed. since the group_concat function can be used, can other functions be used?
Hurry and use the count function for a try. it's as easy as it takes so much time.
Now release the complete statement:
Select *, count (distinct name) from table group by name
Result:
Id name count (distinct name)
1 a 1
2 B 1
3 c 1
The last item is redundant, so you don't have to worry about it.
It turned out that mysql was so stupid that I would just lie to him. now I hope you will not be confused by this problem.
By the way, group by must be placed before order by and limit, otherwise an error will be reported.
Let's talk about the actual example of group:
$ SQL = 'SELECT DISTINCT n. nid, tn. tid, n. title, n. created, ni. thumbpath from {term_node} tn inner join {node} n ON n. nid = tn. nid inner join {node_images} ni ON ni. nid = n. nid where tn. tid IN ('. implode (',', $ tids ). ') order by n. nid DESC ';
$ Res = db_query ($ SQL );
$ T_data = array ();
While ($ r = db_fetch_array ($ res )){
Print_r ($ r );
}
When using this query statement, there will always be two identical nids, such as the following results
Array
(
[Created] = & gt; 1215331278
[Nid] = & gt; 1603
[Tid] => 32
[Title] => DIY green Qinyin for summer weddings
[Thumbpath] => files/node_images/home-77.1_tn.jpg
)
Array
(
[Created] = & gt; 1215331278
[Nid] = & gt; 1603
[Tid] => 32
[Title] => DIY green Qinyin for summer weddings
[Thumbpath] => files/node_images/003_primary_tn.jpg
)
DISTINCT is not used as it works, but nid is unique in the query structure.
Finally, group by is used.
$ SQL = 'SELECT
N. nid, tn. tid, n. title, n. created, ni. thumbpath from {term_node} tn INNER
JOIN {node} n ON n. nid = tn. nid inner join {node_images} ni ON
Ni. nid = n. nid where tn. tid IN ('. implode (', ', $ tids).') GROUP
N. nid DESC ';
$ Res = db_query ($ SQL );
$ T_data = array ();
While ($ r = db_fetch_array ($ res )){
Print_r ($ r );
}
The nid is unique.