Page: 1 2

DISTINCT clause:

DISTINCT clause is not a new concept and almost every database programmer is familiar with it. The DISTINCT clause can only be used with SELECT statements and is used to remove duplicates from the result set. The syntax for the DISTINCT clause is as under

SELECT DISTINCT columns FROM tables WHERE predicates;

Below example show the simplest use of DISTICT clause where the query returns all unique values of column MYCOLUMN1 from the MYTABLE table.

SELECT DISTINCT MYCOLUMN1 FROM MYTABLE;

You can also use DISTINCT clause with more than one field. In such cases the distinct gets applied to each field listed after the DISTINCT keyword. Below query returns each unique combination of MYCOLUMN1 and MYCOLUMN2.

SELECT DISTINCT MYCOLUMN1, MYCOLUMN2 FROM MYTABLE;

Treating DISTINCT clause as a function:

Treating distinct clause as a function is one of the most common mistakes done by most programmers. For example below query is executed with the aim that it will return only distinct values from MYCOLUMN1 and all the values from MYCOLUMN2. Actually that is not possible.

SELECT DISTINCT (MYCOLUMN1), MYCOLUMN2
FROM MYTABLE;

Remember the DISTINCT clause operates on an entire row and not only on a column. There won't be any error but you will get all the results instead of distinct values.

Create Table:

CREATE TABLE MYTABLE (MYCOLUMN1 NVARCHAR(10), MYCOLUMN2 INT);
INSERT INTO MYTABLE VALUES (N'ABC', 10);
INSERT INTO MYTABLE VALUES (N'ABC', 20);
INSERT INTO MYTABLE VALUES (N'XYZ', 30);
INSERT INTO MYTABLE VALUES (N'XYZ', 40);
INSERT INTO MYTABLE VALUES (N'ABC', 50);
INSERT INTO MYTABLE VALUES (N'XYZ', 60);
INSERT INTO MYTABLE VALUES (N'XYZ', 70);
GO

Apply Distinct Clause:

SELECT DISTINCT (MYCOLUMN1), MYCOLUMN2
FROM MYTABLE;

Output:

MYCOLUMN1   MYCOLUMN2
——————- —————–
     ABC              10
     ABC              20
     ABC              50
     XYZ               30
     XYZ               40
     XYZ               60
     XYZ               70

Here we can see that the DISTINCT has not acted as a function. Rather it is a clause applied to entire record. Below three queries are same and will return the same results.

SELECT MYCOLUMN1, MYCOLUMN2 FROM MYTABLE;

SELECT DISTINCT (MYCOLUMN1), MYCOLUMN2 FROM MYTABLE;

SELECT DISTINCT (MYCOLUMN1), (MYCOLUMN2) FROM MYTABLE; Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Recovery from import errors!!
   The Power of Oracle 11g Sequence Enhancements!
   Oracle Data Pump for reorganizing tablespaces!!
   Add Flexibility to your database – Use Database Abstraction Layer