FirebirdSQL logo

COUNT Examples

SELECT
  dept_no,
  COUNT(*) AS cnt,
  COUNT(DISTINCT name) AS cnt_name
FROM employee
GROUP BY dept_no
See also

SELECT.

LIST()

Concatenates values into a string list

Result type

BLOB

Syntax
LIST ([ALL | DISTINCT] <expr> [, separator ])
Table 1. LIST Function Parameters
Parameter Description

expr

Expression.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF that returns the string data type or a BLOB.Fields of numeric and date/time types are converted to strings.Aggregate functions are not allowed as expressions.

separator

Optional alternative separator, a string expression.Comma is the default separator

LIST returns a string consisting of the non-NULL argument values in the group, separated either by a comma or by a user-supplied separator.If there are no non-NULL values (this includes the case where the group is empty), NULL is returned.

  • ALL (the default) results in all non-NULL values being listed.With DISTINCT, duplicates are removed, except if expr is a BLOB.

  • The optional separator argument may be any string expression.This makes it possible to specify e.g. ascii_char(13) as a separator.

  • The expr and separator arguments support BLOBs of any size and character set.

  • Datetime and numeric arguments are implicitly converted to strings before concatenation.

  • The result is a text BLOB, except when expr is a BLOB of another subtype.

  • The ordering of the list values is undefined — the order in which the strings are concatenated is determined by read order from the source set which, in tables, is not generally defined.If ordering is important, the source data can be pre-sorted using a derived table or similar.

    Caution

    This is a trick/workaround, and it depends on implementation details of the optimizer/execution order.This trick doesn’t always work, and it is not guaranteed to work across versions.

    Some reports indicate this no longer works in Firebird 5.0, or only in more limited circumstances than in previous versions.

LIST Examples

  1. Retrieving the list, order undefined:

    SELECT LIST (display_name, '; ') FROM GR_WORK;
  2. Retrieving the list in alphabetical order, using a derived table:

    SELECT LIST (display_name, '; ')
    FROM (SELECT display_name
          FROM GR_WORK
          ORDER BY display_name);
See also

SELECT

MAX()

Maximum

Result type

Returns a result of the same data type the input expression.

Syntax
MAX ([ALL | DISTINCT] <expr>)
Table 1. MAX Function Parameters
Parameter Description

expr

Expression.It may contain a table column, a constant, a variable, an expression, a non-aggregate function or a UDF.Aggregate functions are not allowed as expressions.

MAX returns the maximum non-NULL element in the result set.

  • If the group is empty or contains only NULLs, the result is NULL.

  • If the input argument is a string, the function will return the value that will be sorted last if COLLATE is used.

  • This function fully supports text BLOBs of any size and character set.

Note

The DISTINCT parameter makes no sense if used with MAX() as it doesn’t change the result;it is implemented only for compliance with the standard.