DISTINCT Function Teradata

All of the previous operations of the SELECT returned a row from a table based on its existence in a table. As a result, if multiple rows contain the same value, they all are displayed.

Sometimes it is only necessary to see one of the values, not all. Instead of contemplating a WHERE clause to accomplish this task, the DISTINCT can be added in the SELECT to return unique values by eliminating duplicate values.

The syntax for using DISTINCT:

SELECT DISTINCT <column-name> [, <column-name> ... ]
FROM <table-name>

;

The next SELECT uses DISTINCT to return only one row for display when a value exists:

SELECT DISTINCT Class_code FROM student_table ORDER BY class_code; 5 Rows ReturnedDISTINCT Function

There are a couple noteworthy situations in the above output. First, although there are three freshman, two sophomores, two juniors, two seniors and one row without a class code, only one output row is returned for each of these values. Lastly, the NULL is considered a unique value whether there is one row or multiple rows containing it. So, it is displayed one time.

The main considerations for using DISTINCT, it must:

  1. Appear only once
  2. Apply to all columns listed in the SELECT to determine uniqueness
  3. Appear before the first column name

The following SELECT uses more than one column with a DISTINCT:

SELECT DISTINCT class_code
,grade_pt
FROM student_table
ORDER BY class_code, grade_pt;
10 Rows ReturnedSELECT uses more than one column with a DISTINCT:

The DISTINCT in this SELECT returned all ten rows of the table. This is due to the fact that when the class code and the grade point are combined for comparison, they are all unique. The only potential for a duplicate exists when two students in the same class have the same grade point average. Therefore, as more and more columns are listed in a SELECT with a DISTINCT, there is a greater opportunity for more rows to be returned due to a higher likelihood for unique values.

If, when using DISTINCT, spool space is exceeded, use of the GROUP BY versus DISTINCT for eliminating duplicate rows. It may solve the problem.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics