Tips and use of distinct in select in Hive

The following is reproduced content

Single-table only query by: DISTINCT
Unique query for multiple tables: Group by
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, rather than using it to return all values of the non-repeating record. The reason is that distinct can only return its target field, and can not return other fields, with distinct can not solve, I only use a double-loop query to solve, and so for a very large number of stations, will undoubtedly directly affect the efficiency.
Let's take a look at the example below:
The structure of the table is as follows:
ID Name
1 A
2 b
3 C
4 C
5 b
The basic table structure is probably the case, this is just a simple example, the actual multi-table query and so on, and so the situation is 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
How distinct didn't work. The role is actually up, but he also functions two fields, that is, must have the same ID and 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. It is difficult to put distinct in the Where condition. Try it and give an error.

Tried for a half day other can think of method also not, finally found a usage in MySQL manual, with group_concat (distinct name) with 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 despair.
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, and since you can use the Group_concat function, can the other functions be OK?
Hurriedly with the Count function a try, success, cost so much effort, originally 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 is achieved.
The original MySQL so stupid, gently put him to cheat, now take out hope you don't be this problem toss.
By the way, group by must be placed before order by and limit, otherwise the error will be reported.
Say the actual example of GROUP by:
$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.ni D 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, there will always be two of the same nid, such as the following results
Array
(
[Created] = 1215331278
[Nid] = 1603
[Tid] = 32
[title] + Summer Wedding green drink DIY
[Thumbpath] = files/node_images/home-77.1_tn.jpg
)
Array
(
[Created] = 1215331278
[Nid] = 1603
[Tid] = 32
[title] + Summer Wedding green drink DIY
[Thumbpath] = files/node_images/003_primary_tn.jpg
)
The above use of distinct also no use, in fact, it is useful, but I would like to query the structure of the NID is unique.
Finally, GROUP by
$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 by
N.nid DESC ';
$res = Db_query ($sql);
$t _data = Array ();
while ($r = Db_fetch_array ($res)) {
Print_r ($R);
}
I got the NID to be the only one.