How to use Oracle Distinct

Distinct this keyword to filter out the extra duplicate records only one, but often use it only to return the number of records not duplicates, rather than using it to return all the values not to be recorded. The reason is that distinct only use the double loop query to solve, and this for a very large amount of data station, it will undoubtedly directly affect the efficiency.

Sql>--Create demo table

Sql> CREATE TABLE Employee (

2 ID VARCHAR2 (4 BYTE) not NULL,

3 first_name VARCHAR2 (BYTE),

4 last_name VARCHAR2 (BYTE),

5 start_date Date,

6 end_date Date,

7 Salary Number (8,2),

8 City VARCHAR2 (BYTE),

9 Description VARCHAR2 (MB)

10)

11/

Table created.

Sql>

Sql>--Prepare data

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Jason ', ' Martin ', to_date (' 19960725 ', ' YYYYMMDD '), to_date (' 20060725 ', ' YYYYMMDD '), 1234.56, ' Toronto ', ' Programmer ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Alison ', ' Mathews ', To_date (' 19760321 ', ' YYYYMMDD '), to_date (' 19860221 ', ' YYYYMMDD '), 2334.78, ' Vancouver ', ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (', ' James ', ' Smith ', to_date (' 19781212 ', ' YYYYMMDD '), to_date (' 19900315 ', ' YYYYMMDD '), 2334.78, ' Vancouver ' , ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Celia ', ' Rice ', to_date (' 19821024 ', ' YYYYMMDD '), to_date (' 19990421 ', ' YYYYMMDD '), 2334.78, ' Vancouver ', ' Manager ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Robert ', ' Black ', to_date (' 19840115 ', ' YYYYMMDD '), to_date (' 19980808 ', ' YYYYMMDD '), 2334.78, ' Vancouver ', ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' ', ' Linda ', ' Green ', to_date (' 19870730 ', ' YYYYMMDD '), to_date (' 19960104 ', ' YYYYMMDD '), 2334.78, ' New York ', ' Tester ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (' Modified ', ' David ', ' Larry ', to_date (' 19901231 ', ' YYYYMMDD '), to_date (' 19980212 ', ' YYYYMMDD '), 2334.78, ' New York ', ' Manager ')

3/

1 row created.

sql> INSERT into Employee (IDs, first_name, last_name, start_date, End_date, Salary, City, Description)

2 values (', ' James ', ' Cat ', to_date (' 19960917 ', ' YYYYMMDD '), to_date (' 20020415 ', ' YYYYMMDD '), 2334.78, ' Vancouver ', ' Tester ')

3/

1 row created.

Sql>

Sql>

Sql>

Sql>--Display data in the table

Sql> SELECT * from Employee

2/

ID first_name last_name start_dat end_date SALARY City DESCRIPTION

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

Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer

Alison Mathews 21-mar-76 21-feb-86 2334.78 Vancouver Tester

James Smith 12-dec-78 15-mar-90 2334.78 Vancouver Tester

Celia Rice 24-oct-82 21-apr-99 2334.78 Vancouver Manager

Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester

Linda Green 30-jul-87 04-jan-96 2334.78 New York Tester

David Larry 31-dec-90 12-feb-98 2334.78 New York Manager

James Cat 17-sep-96 15-apr-02 2334.78 Vancouver Tester

8 rows selected.

Sql>

Sql>

Sql>

Sql>

Sql>

Sql>-Remember The DISTINCT operator applies to the entire select list.

Sql>

Sql> SELECT DISTINCT City, Description from Employee;

City DESCRIPTION

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

New York Manager

Vancouver Tester

Toronto Programmer

Vancouver Manager

New York Tester

Use with groupy count at the same time

Sql> Select coder

2, COUNT (distinct course)

3, COUNT (*)

4 from offerings

5 Group by Coder;

Coder Count (Distinctcourse) count (*)

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

1 2 3

4 2 2

8 2 2

11 1 1

13 2 2

3 3

6 rows selected.

Note : Please pay attention to the triple programming Tutorials section for more wonderful articles .