The distinct keyword is used to filter out redundant duplicate records and keep only one.But often it is only used to return the number of unique records,Instead of using it to return all values ​​that are not re-recorded.The reason is that distinct can only be solved with a double loop query,And for a station with a very large amount of data,No doubt it will directly affect efficiency.

Let's look at the example first:


Field 1 field 2 id name 1 a 2 b 3 c 4 c 5 b

The library structure looks like this,This is just a simple example,The reality is much more complicated.

For example, I want to use a single statement to get all the data with unique names.Then you must use distinct to remove redundant duplicate records.

The result obtained by selecting distinct name from table is:


name a b c

It seems to be working,But what I want is the id value?Change the query:

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 work?The effect is working,But he worked on both fields at the same time,That is, only those with the same id and name will be excluded.. . . . . .

Let's change the query again:

select id, distinct name from table

Unfortunately, you get nothing but error messages,distinct must be placed at the beginning.Is it difficult to put distinct into the where condition?Yes, I still report an error.

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

The following methods are also not feasible:

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


ora-00979:not a group by expression00979. 00000-"not a group by expression"

Still reporting errors,

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;


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

Second, multiple fields

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


1 zjx 001

2 zjx 002

Third, a single field

select distinct t.name from test1 t


1 zjx

  • Previous Tips for copying files with scp under Linux without entering a password
  • Next Android list dialog usage example analysis