Books in black and white
 Books Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics

# Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Previous << 1 .. 97 98 99 100 101 102 < 103 > 104 105 106 107 108 109 .. 241 >> Next

Example 2

Example 3

Example 4

Example 5

Example 6: Case Sensitivity

The following example substitutes a positive integer (3) for YrsExp in the ORDER BY clause. The 3 refers to the numeric position of YrsExp in the select expr-list.

SELECT Name, JobTitle, YrsExp FROM Employee ORDER BY 3;

The following example produces a list of employees, sorted by DeptNo and Salary (note that Salary is not contained in the select expr-list).

SELECT Name, DeptNo FROM Employee ORDER BY DeptNo, Salary;

The following example returns a list of each department number and its total population, in the order of lowest-population first:

SELECT COUNT(Name), DeptNo FROM Employee GROUP BY DeptNo,

ORDER BY COUNT(Name) ;

Each of the following statements may be used to list employees by department number, with the highest-paid employee listed first and the lowest-paid last:

SELECT Name, DeptNo, Salary FROM Employee ORDER BY 2 ASC, 3 DESC;

SELECT Name, DeptNo, Salary FROM Employee ORDER BY DeptNo, Salary DESC;

Consider the following statements that create and populate table T:

CREATE TABLE T (A CHAR(4) NOT CASESPECIFIC, B BYTEINT)

PRIMARY INDEX (A,B)
INSERT INTO T VALUES ( AAAA' 1)
INSERT INTO T VALUES ( aaaa' 2)
INSERT INTO T VALUES ( BBBB' 3)
INSERT INTO T VALUES ( bbbb' 4)

If the default handling of case is allowed, the following request,

SELECT * FROM T ORDER BY A ;

produces:

A B

AAAA

aaaa

BBBB

bbbb

Teradata RDBMS for UNIX SQL Reference

7-39
Queries: The SELECT Statement

ORDER BY Clause

On the other hand, when CASESPECIFIC (CS) is used:

SELECT * FROM T ORDER BY CAST(A AS CASESPECIFIC);

or

SELECT CAST(A AS CASESPECIFIC), B FROM T ORDER BY 1;

the results will be one of the following, depending on the collation in effect for the session:

EBCDIC ASCII MULTINATIONAL
A B A B A B
aaaa 2 AAAA 1 aaaa 2
bbbb 4 BBBB 3 AAAA 1
AAAA 1 aaaa 2 bbbb 4
BBBB 3 bbbb 4 BBBB 3

7-40

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

DISTINCT and ALL Options

DISTINCT and ALL Options

The DISTINCT option specifies that duplicate values not be returned when an expression is processed.The DISTINCT option of the SELECT statement eliminates duplicate rows from the result

The ALL option is the default situation, and indicates that all rows are to be returned.

Syntax

Usage Notes

Examples

Example 1

— DISTINCT —

--- ALL------

FF06A018

where:

Syntax Element . . . Specifies that . . .
DISTINCT only one row is to be returned from any set of duplicates that might result from a given expression list. Two rows are considered duplicates only if each value in one is equal to the corresponding value in the other.
ALL all rows, including duplicates, are to be returned in the results of the expression list. This is the default value. It is optional in the query (Set Operator) expressions. Refer to the following section.

When the DISTINCT option is used, the SELECT statement cannot contain a WITH clause.

The following examples illustrate the use of the DISTINCT and ALL options.

The following example returns the number of unique job titles.

SELECT COUNT(DISTINCT JobTitle) FROM...

Teradata RDBMS for UNIX SQL Reference

7-41
Queries: The SELECT Statement

DISTINCT and ALL Options

In a single query, only one expression can be used as the argument for an aggregate operator that uses the DISTINCT option. However, more than one aggregate operator can use DISTINCT with other occurrences of the expression in the same query.

SELECT SUM(DISTINCT x), AVG(DISTINCT x) FROM...

DISTINCT aggregate operations can be done only at the first level of aggregation.

The following statement can be used to list unique department Example 3 7 numbers in the Employee table:

SELECT DISTINCT DeptNo FROM Employee ;

The result returned is:

DeptNo

100

300

500

600

700

Example 2: DISTINCT With More Than One Aggregate Operator

In contrast, the following statement returns a department number

Example 4: Using ALL 7 for every employee.

SELECT ALL Deptno FROM Employee ;

ALL is the default option, except in query (Set Operator) expressions, where ALL is an option, but not the default.

7-42

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

Query Expressions (Set Operators)

Query Expressions (Set Operators)

A query expression is a set of queries combined by the set operators IntrCduCUon UNION, INTERSECT, and EXCEPT (also called MINUS).

The query expressions allow you to manipulate the answers to two or more queries by combining the results of each query into a single result set.

When query expressions are used, the data type, title, and format clauses contained in the first SELECT statement determine the data type, title, and format information which appear in the final result. This information is ignored in all other SELECT statements in the query.
Previous << 1 .. 97 98 99 100 101 102 < 103 > 104 105 106 107 108 109 .. 241 >> Next