Oracle 5.0 Reference Manual Download

Page: 1105 / 3232

SELECT

Syntax

1085

•

GROUP BY

permits a

WITH ROLLUP

modifier. See

Section 12.15.2, “

GROUP BY

Modifiers”

• The

HAVING

clause is applied nearly last, just before items are sent to the client, with no

optimization. (

LIMIT

is applied after

HAVING

clause can refer to any column or alias named in a

select_expr

in the

SELECT

list or

in outer subqueries, and to aggregate functions. However, the SQL standard requires that

HAVING

must reference only columns in the

GROUP BY

clause or columns used in aggregate functions. To

accommodate both standard SQL and the MySQL-specific behavior of being able to refer columns in
the

SELECT

list, MySQL 5.0.2 and up permit

HAVING

to refer to columns in the

SELECT

list, columns

in the

GROUP BY

clause, columns in outer subqueries, and to aggregate functions.

For example, the following statement works in MySQL 5.0.2 but produces an error for earlier
versions:

mysql>

SELECT COUNT(*) FROM t GROUP BY col1 HAVING col1 = 2;

If the

HAVING

clause refers to a column that is ambiguous, a warning occurs. In the following

statement,

col2

is ambiguous because it is used as both an alias and a column name:

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Preference is given to standard SQL behavior, so if a

HAVING

column name is used both in

GROUP

and as an aliased column in the output column list, preference is given to the column in the

GROUP BY

column.

• Do not use

HAVING

for items that should be in the

WHERE

clause. For example, do not write the

following:

SELECT

col_name

FROM

tbl_name

HAVING

col_name

> 0;

Write this instead:

SELECT

col_name

FROM

tbl_name

WHERE

col_name

> 0;

• The

HAVING

clause can refer to aggregate functions, which the

WHERE

clause cannot:

SELECT user, MAX(salary) FROM users

GROUP BY user HAVING MAX(salary) > 10;

(This did not work in some older versions of MySQL.)

• MySQL permits duplicate column names. That is, there can be more than one

select_expr

with

the same name. This is an extension to standard SQL. Because MySQL also permits

GROUP BY

and

HAVING

to refer to

select_expr

values, this can result in an ambiguity:

SELECT 12 AS a, a FROM t GROUP BY a;

In that statement, both columns have the name

. To ensure that the correct column is used for

grouping, use different names for each

select_expr

• MySQL resolves unqualified column or alias references in

ORDER BY

clauses by searching in the

select_expr

values, then in the columns of the tables in the

FROM

clause. For

GROUP BY

HAVING

clauses, it searches the

FROM

clause before searching in the

select_expr

values. (For

GROUP BY

and

HAVING

, this differs from the pre-MySQL 5.0 behavior that used the same rules as

for

ORDER BY

• The

LIMIT

clause can be used to constrain the number of rows returned by the

SELECT

statement.

LIMIT

takes one or two numeric arguments, which must both be nonnegative integer constants

(except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second
specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands