A detailed explanation of the usage of distinct in Oracle _oracle

Let's take a look at the following examples:

Table tables

Field 1 Field 2 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.

The result of the select DISTINCT name from table is:

----------

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

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

The following method is also not OK:

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

Results:

Ora-00979:not a GROUP BY expression
00979.00000-"Don't a group By expression"

Still complain,

Group by must be placed before order by and limit, or it will be an error

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

I think it works.

Select Max (ID), name from table group by name;

Results:

ID Name
1 A
2 b
4 C
5 D

Usage Two:

I. Data:

1 ZJX 001 AAAIBZAAQAAAAVPAAA
2 ZJX 002 Aaaibzaaqaaaavpaab
3 ZJX 001 AAAIBZAAQAAAAVPAAC

Two, multiple fields

Select distinct T.name,t.code from test1 t
select distinct t.* from Test1 t

Results:

1 ZJX 001
2 ZJX 002

Three, the word paragraph

Select distinct t.name from Test1 t

Results:

1 ZJX