•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 a . To ensure that the correct column is used for grouping, use different names for each select_expr . 

•The WINDOW  clause, if present, defines named windows that can be referred to by window functions. For details, see Section 12.21.4, “Named Windows” . 

•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  or 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, with these exceptions: 

•Within prepared statements, LIMIT  parameters can be specified using ?  placeholder markers. 

•Within stored programs, LIMIT  parameters can be specified using integer-valued routine parameters or local variables. 

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): 

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15 

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last: 

SELECT * FROM tbl LIMIT 95,18446744073709551615; 

With one argument, the value specifies the number of rows to return from the beginning of the result set: 

SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows 

In other words, LIMIT row_count  is equivalent to LIMIT 0, row_count . 

For prepared statements, you can use placeholders. The following statements return one row from the tbl  table: 

