Oracle-distinct usage

The distinct keyword is used to filter out redundant duplicate records and keep only one, but it is often only used to return the number of unique records instead of using it to return all values ​​of unique records. The reason is that distinct can only be solved by double loop query, which will undoubtedly directly affect the efficiency of a website with a very large amount of data.

Let’s take a look at the example:

table table

Field 1                                                                                                            5              b

The library structure is roughly like this. This is just a simple example. The actual situation will be more complicated. many.

For example, if I want to use one statement to query all data with non-duplicate names, then I must use distinct to remove redundant duplicate records.

select distinct name from table The result is:

----------

name a b c

It seems to have achieved the effect, 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

How come distinct doesn’t work? It works, but it affects two fields at the same time, that is, the id and name must be the same to be excluded. . . . . . .

Let’s change the query statement again:

select id, distinct name from table

Unfortunately, you can’t get anything except error information, distinct must be placed at the beginning. Is it so difficult to put distinct in the where condition? Yes, it still reports an error.

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

The following method is not feasible:

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

Result:

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

still reports an error,

group by must be placed before order by and limit, otherwise an error will be reported

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

I think this is feasible

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

Result:

id name

1 a

2 b

4 c

5 d