SELECT

Aggregate functions: Extended functionality

Table of Contents

Mixing aggregate functions from different contexts
Aggregate functions and GROUP BY items inside subqueries
Subqueries inside aggregate functions
Nesting aggregate function calls
Aggregate statements: Stricter HAVING and ORDER BY

Changed in: 1.5

Description: Several types of mixing and nesting aggragate functions are supported since Firebird 1.5. They will be discussed in the following subsections. To get the complete picture, also look at the SELECT :: GROUP BY sections.

Mixing aggregate functions from different contexts

Firebird 1.5 and up allow the use of aggregate functions from different contexts inside a single expression.

Example:

select
  r.rdb$relation_name as "Table name",
  ( select max(i.rdb$statistics) || ' (' || count(*) || ')'
    from rdb$relation_fields rf
    where rf.rdb$relation_name = r.rdb$relation_name
  ) as "Max. IndexSel (# fields)"
from
  rdb$relations r
  join rdb$indices i on (i.rdb$relation_name = r.rdb$relation_name)
group by r.rdb$relation_name
having max(i.rdb$statistics) > 0
order by 2

This admittedly rather contrived query shows, in the second column, the maximum index selectivity of any index defined on a table, followed by the table's field count between parentheses. Of course you would normally display the field count in a separate column, or in the column with the table name, but the purpose here is to demonstrate that you can combine aggregates from different contexts in a single expression.

Warning

Firebird 1.0 also executes this type of query, but gives the wrong results!

Aggregate functions and GROUP BY items inside subqueries

Since Firebird 1.5 it is possible to use aggregate functions and/or expressions contained in the GROUP BY clause inside a subquery.

Examples:

This query returns each table's ID and field count. The subquery refers to flds.rdb$relation_name, which is also a GROUP BY item:
select
  flds.rdb$relation_name as "Relation name",
  ( select rels.rdb$relation_id
    from rdb$relations rels
    where rels.rdb$relation_name = flds.rdb$relation_name
  ) as "ID",
  count(*) as "Fields"
from rdb$relation_fields flds
group by flds.rdb$relation_name
The next query shows the last field from each table and and its 1-based position. It uses the aggregate function MAX in a subquery.
select
  flds.rdb$relation_name as "Table",
  ( select flds2.rdb$field_name
    from rdb$relation_fields flds2
    where
      flds2.rdb$relation_name = flds.rdb$relation_name
      and flds2.rdb$field_position = max(flds.rdb$field_position)
  ) as "Last field",
  max(flds.rdb$field_position) + 1 as "Last fieldpos"
from rdb$relation_fields flds
group by 1
The subquery also contains the GROUP BY item flds.rdb$relation_name, but that's not immediately obvious because in this case the GROUP BY clause uses the column number.

Subqueries inside aggregate functions

Using a singleton subselect inside (or as) an aggregate function argument is supported in Firebird 1.5 and up.

Example:

select
  r.rdb$relation_name as "Table",
  sum( (select count(*)
        from rdb$relation_fields rf
        where rf.rdb$relation_name = r.rdb$relation_name)
  ) as "Ind. x Fields"
from
  rdb$relations r
  join rdb$indices i
    on (i.rdb$relation_name = r.rdb$relation_name)
group by
  r.rdb$relation_name

Nesting aggregate function calls

Firebird 1.5 allows the indirect nesting of aggregate functions, provided that the inner function is from a lower SQL context. Direct nesting of aggregate function calls, as in “COUNT( MAX( price ) )”, is still forbidden and punishable by exception.

Example: See under Subqueries inside aggregate functions, where COUNT() is used inside a SUM().

Aggregate statements: Stricter HAVING and ORDER BY

Firebird 1.5 and above are stricter than previous versions about what can be included in the HAVING and ORDER BY clauses. If, in the context of an aggregate statement, an operand in a HAVING or ORDER BY item contains a column name, it is only accepted if one of the following is true:

The column name appears in an aggregate function call (e.g. “HAVING MAX(SALARY) > 10000”).
The operand equals or is based upon a non-aggregate column that appears in the GROUP BY list (by name or position).

“Is based upon” means that the operand need not be exactly the same as the column name. Suppose there's a non-aggregate column “STR” in the select list. Then it's OK to use expressions like “UPPER(STR)”, “STR || '!'” or “SUBSTRING(STR FROM 4 FOR 2)” in the HAVING clause – even if these expressions don't appear as such in the SELECT or GROUP BY list.

Ambiguous JOIN statements rejected

Changed in: 1.0

Description: InterBase 6 accepts and executes statements like the one below, which refers to an unqualified column name even though that name exists in both tables participating in the JOIN:

select buses.name, garages.name
  from buses join garages on buses.garage_id = garage.id
  where name = 'Phideaux III'

The results of such a query are unpredictable. Firebird Dialect 3 returns an error if there are ambiguous field names in JOIN statements. Dialect 1 gives a warning but will execute the query anyway.

[AS] before relation alias

Added in: IB

Description: The keyword AS can optionally be placed before a relation alias, just as it can be placed before a column alias. This feature dates back to InterBase times, but wasn't documented in the IB Language Reference.

Syntax:

SELECT ... FROM <relation> [AS] alias

<relation>  ::=  A table, view, or selectable SP

Examples:

select order_no, total, fullname
  from orders as o join customers as c on o.cust_id = c.cust_id

select order_no, total, fullname
  from orders o join customers c on o.cust_id = c.cust_id

The two queries are fully equivalent.

FIRST and SKIP

Added in: 1.0

Changed in: 1.5

Description: FIRST limits the output of a query to the first so-many rows. SKIP will suppress the given number of rows before starting to return output.

Syntax:

SELECT [FIRST (<int-expr>)] [SKIP (<int-expr>)] <columns> FROM ...

<int-expr>  ::=  Any expression evaluating to an integer.
<columns>   ::=  The usual output column specifications.
Note

If <int-expr> is an integer literal or a query parameter, the “()” may be omitted. Subselects on the other hand require an extra pair of parentheses.

FIRST and SKIP are both optional. When used together as in “FIRST m SKIP n”, the n topmost rows of the output set are discarded and the first m rows of the remainder are returned.

SKIP 0 is allowed, but of course rather pointless. FIRST 0 is allowed in version 1.5 and up, where it returns an empty set. In 1.0.x, FIRST 0 causes an error. Negative SKIP and/or FIRST values always result in an error.

If a SKIP lands past the end of the dataset, an empty set is returned. If the number of rows in the dataset (or the remainder after a SKIP) is less than the value given after FIRST, that smaller number of rows is returned. These are valid results, not error situations.

Examples:

The following query will return the first 10 names from the People table:
select first 10 id, name from People
  order by name asc
The following query will return everything but the first 10 names:
select skip 10 id, name from People
  order by name asc
And this one returns the last 10 rows. Notice the double parentheses:
select skip ((select count(*) - 10 from People))
  id, name from People
  order by name asc
This query returns rows 81–100 of the People table:
select first 20 skip 80 id, name from People
  order by name asc

Two Gotchas with FIRST in subselects

This:
```
delete from MyTable where ID in (select first 10 ID from MyTable)
```
will delete all of the rows in the table. Ouch! The sub-select is evaluating each 10 candidate rows for deletion, deleting them, slipping forward 10 more... ad infinitum, until there are no rows left. Beware!
Queries like:
```
...where F1 in (select first 5 F2 from Table2 order by 1 desc)
```
won't work as expected, because the optimization performed by the engine transforms the IN predicate to the correlated EXISTS predicate shown below. It's obvious that in this case FIRST N doesn't make any sense:
```
...where exists
   ( select first 5 F2 from Table2
     where Table2.F2 = Table1.F1
     order by 1 desc )
```

GROUP BY `UDF`

Changed in: 1.0

Description: In Firebird, you can use the output of a user-defined function as a GROUP BY item.

Syntax:

SELECT ... FROM ...
   GROUP BY <item> [, <item> ...]
   ...

<item>      ::=  column-name [COLLATE collation-name] | <udf-call>
<udf-call>  ::=  udf-name(arg1 [, argN ...])

UDF calls may be nested, but – as follows from the syntax – you cannot mix UDF calls and COLLATE in a single GROUP BY item.

Example:

select strlen(lastname), count(*)
  from people
  group by strlen(lastname)
  order by 2 desc

Warning

DSQL currently lacks a mechanism to check if GROUP BY UDF subclauses are formulated correctly. Always make sure that your GROUP BY item list correctly represents the scalar (i.e. non-aggregate) expression(s) in your SELECT list.

GROUP BY internal function, column position, and CASE

Changed in: 1.5

Description: Firebird 1.5 adds the following to the list of valid GROUP BY items:

1-based column position numbers (like in ORDER BY);
The internal functions COALESCE, EXTRACT, NULLIF, SUBSTRING and UPPER;
CASE constructs.

Syntax:

SELECT ... FROM ...
   GROUP BY <item> [, <item> ...]
   ...

<item>           ::=  column-name [COLLATE collation-name]
                      | column-position
                      | <function-call>
                      | CASE-construct

<function-call>  ::=  COALESCE(arg1, arg2 [, argN ...])
                      | EXTRACT(part FROM date/time)
                      | NULLIF(arg1, arg2)
                      | SUBSTRING(str FROM pos [FOR count])
                      | UPPER(str)
                      | udf-name(arg1 [, argN ...])

Function calls may be nested. As in previous versions, COLLATE can only be used with column names.

If you group by a column position, the expression at that position is copied internally from the select list. If it concerns a subquery, that subquery will be executed at least twice.

Important

A GROUP BY item cannot be a reference to an aggregate function (including those that are buried inside an expression) from the same context.
As before, every non-aggregate column must appear in the GROUP BY list, whether explicitly or by position.
An exception to the previous rule are non-aggregate subquery columns; you may leave these out of the GROUP BY clause. Be very careful though: if the subquery uses columns that are not in the GROUP BY list, it may return different results within the same group and you're in trouble. To avoid this, add those columns – or the entire subquery column – to the GROUP BY.

Examples:

select
  case when price is null then 0 else price end,
  sum(number_sold)
from sales_per_article
group by
  case when price is null then 0 else price end
Of course this example is only to demonstrate the use of a CASE construct in the GROUP BY clause. In this particular case you should first of all use COALESCE:
select
  coalesce (price, 0),
  sum(number_sold)
from sales_per_article
group by
  coalesce (price, 0)
and then you could save yourself some typing time by using the column number:
select
  coalesce (price, 0),
  sum(number_sold)
from sales_per_article
group by 1

HAVING: Stricter rules

Changed in: 1.5

Description: See Aggregate statements: Stricter HAVING and ORDER BY.

ORDER BY: Expressions and `NULL`s placement

Changed in: 1.5

Description: In addition to column names and positions, the ORDER BY clause can now also contain expressions to sort the output by. Furthermore, per-column NULLS FIRST and NULLS LAST subclauses can be used to specify where NULLs appear in the sorted column.

Syntax:

SELECT ... FROM ...
   ...
   ORDER BY <ordering-item> [, <ordering-item> ...]

<ordering-item>  ::=  {column-name | column-position | expression}
                         [COLLATE collation-name]
                         [ASC[ENDING] | DESC[ENDING]]
                         [NULLS {FIRST|LAST}]

Expressions consisting of a single non-negative number will be interpreted as 1-based column numbers and will cause an exception if they're not in the range from 1 to the number of columns.

By default, NULLs will be placed at the end of the sort, regardless whether the order is ascending or descending. This is the same behaviour as in previous Firebird versions. No index will be used on columns for which the non-default NULLS FIRST placement is chosen.

The number of function or procedure invocations resulting from a sort based on a UDF or stored procedure is unpredictable, regardless whether the ordering is specified by the expression itself or by the column position number.

Examples:

select * from msg
  order by process_time desc nulls first

select first 10 * from document
  order by strlen(description) desc

select doc_number, doc_date from payorder
union all
select doc_number, doc_date from budgorder
  order by 2 desc nulls last, 1 asc nulls first

ORDER BY: Stricter rules with aggregate statements

Changed in: 1.5

Description: See Aggregate statements: Stricter HAVING and ORDER BY.

WITH LOCK

Available in: DSQL, PSQL

Added in: 1.5

Description: WITH LOCK provides a limited explicit pessimistic locking capability for cautious use in conditions where the affected row set is:

extremely small (ideally, a singleton), and
precisely controlled by the application code.

This is for experts only!

The need for a pessimistic lock in Firebird is very rare indeed and should be well understood before use of this extension is considered.

It is essential to understand the effects of transaction isolation and other transaction attributes before attempting to implement explicit locking in your application.

Syntax:

SELECT ... FROM single_table
   [WHERE ...]
   [FOR UPDATE [OF ...]]
   [WITH LOCK]

If the WITH LOCK clause succeeds, it will secure a lock on the selected rows and prevent any other transaction from obtaining write access to any of those rows, or their dependants, until your transaction ends.

If the FOR UPDATE clause is included, the lock will be applied to each row, one by one, as it is fetched into the server-side row cache. It becomes possible, then, that a lock which appeared to succeed when requested will nevertheless fail subsequently, when an attempt is made to fetch a row which becomes locked by another transaction.

WITH LOCK can only be used with a top-level, single-table SELECT statement. It is not available:

in a subquery specification;
for joined sets;
with the DISTINCT operator, a GROUP BY clause or any other aggregating operation;
with a view;
with the output of a selectable stored procedure;
with an external table.

A lengthier, more in-depth discussion of “SELECT ... WITH LOCK” is included in the Notes. It is a must-read for everybody who considers using this feature.

SELECT

Tip

Aggregate functions: Extended functionality

Mixing aggregate functions from different contexts

Warning

Aggregate functions and GROUP BY items inside subqueries

Subqueries inside aggregate functions

Nesting aggregate function calls

Aggregate statements: Stricter HAVING and ORDER BY

Ambiguous JOIN statements rejected

[AS] before relation alias

FIRST and SKIP

Note

Two Gotchas with FIRST in subselects

GROUP BY UDF

Warning

GROUP BY internal function, column position, and CASE

Important

HAVING: Stricter rules

ORDER BY: Expressions and NULLs placement

ORDER BY: Stricter rules with aggregate statements

WITH LOCK

This is for experts only!

GROUP BY `UDF`

ORDER BY: Expressions and `NULL`s placement