Explain the usage of distinct in oracle, oracledistinct

Explain the usage of distinct in oracle, oracledistinct

Let's take a look at the example below:

Table

Field 1 Field 2 id name 1 a 2 B 3 c 4 c 5 B

The library structure is like this. This is just a simple example, and the actual situation 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:

----------

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 a 2 B 3 c 4 c 5 B

How does distinct not work? It works, but it also applies to 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? Yes. An error is returned.

Bytes ------------------------------------------------------------------------------------------------------------

The following method is not feasible:

select *, count(distinct name) from table group by name

Result:

ORA-00979: not a GROUP BY expression00979. 00000 - "not a GROUP BY expression"

Still report error,

Group by must be placed before order by and limit. Otherwise, an error will be reported.

Bytes ------------------------------------------------------------------------------------------------------------

I think this is feasible

select max(id), name from table group by name;

Result:

Id name
1
2 B
4 c
5 d

Usage 2:

I. Data:

1 zjx 001 AAAiBZAAQAAAAVPAAA
2 zjx 002 AAAiBZAAQAAAAVPAAB
3 zjx 001 AAAiBZAAQAAAAVPAAC

2. Multiple Fields

select distinct t.name,t.code from test1 tselect distinct t.* from test1 t

Result:

1 zjx 001
2 zjx 002

3. Single field

select distinct t.name from test1 t

Result:

1 zjx