Enhancements to GROUP BY and ORDER BY

Go Up to SELECT

For: 2017 Update 1 and above only.

This section describes enhancements to GROUP BY and ORDER BY syntax that are not necessarily SQL standard, but nonetheless allow expressive behavior of grouping and ordering query result sets.

Usability

GROUP BY has been enhanced to take in an ordinal value (INT) referring to a select list item position. In addition, both GROUP BY and ORDER BY have been enhanced to take in an "expr" that would result in a single value.

syntax:

SELECT [TRANSACTION transact] [DISTINCT | ALL] {* | <val> [, <val> …]} [INTO :var [, :var …]]
  FROM <tableref> [, <tableref> …]
[WHERE <search_condition>]
[GROUP BY {col | INT | expr} [COLLATE collation] [, {col | INT | expr} [COLLATE collation] …] [HAVING <search_condition>]
[UNION [ALL] select_expr][PLAN <plan_expr>]
[ORDER BY <order_list>]
[ROWS VALUE [TO upper_value] [BY step_value][PERCENT][WITH TIES]]
[FOR UPDATE [OF col [, col …]]];
  
order_list = {col | INT | expr} [COLLATE collation]
  [ASC[ENDING] | DESC[ENDING]]
  [, order_list …]
  
expr ::= A valid SQL expression that results in a single value.
Index definition using a COMPUTED BY <expression> 
CREATE TABLE t1 (
  dummy0 integer,
  f1 integer not null,
  f2 integer not null,
  f3 integer not null,
  f3_min_f2 computed by (f3 - f2),
  first_name VARCHAR(10) DEFAULT 'Give me ',
  last_name VARCHAR(10) DEFAULT 'a name'
);
  
/* optional expression index definitions to illustrate optimized queries using the index */
CREATE INDEX idx_expr_t1_mul ON t1 COMPUTED BY (f1 * f2);
CREATE INDEX idx_expr_t1_udf ON t1 COMPUTED BY (UPPER(first_name) || UPPER(last_name));

Sample queries:

The query optimizer in InterBase will tries to match up the index, if available, to expressions in the query that match the index definitions.

/* Following sample queries are optimized to use the expression index, if available; SET PLAN ON to see the plan.
   Otherwise, the ORDER BY and GROUP BY <expr> will execute by sorting the result set as needed. */
 
/* ORDER BY */
-- by expression
select f1, f2, f1 * f2 from t1 ORDER BY (f1 * f2);
-- by ordinal position
select (f1 * f2), (f1 + f2) from t1 WHERE f1 * f2 = 100 ORDER BY 1, 2;
select f1 * f2 from t1 PLAN (T1 ORDER IDX_EXPR_T1_MUL) ORDER BY 1;
  
/* GROUP BY */
-- by ordinal position
select f1 * f2, count(*) from t1 GROUP BY 1;
-- by expression
select (f1 * f2), count(*) from t1 GROUP BY (f1 * f2);

Requirements and Constraints

An expression in a GROUP BY or ORDER BY will only be optimized to use an underlying index if Expression Index support is enabled in the database engine version you are using. Please note that Expression Index support is available only starting with InterBase 2017 Update 1 version database engine.
UNION ALL queries with ORDER BY <expr> are not optimized to use any underlying Expression Index, yet.

Migration issues

None. As these are new extensions to existing support for GROUP BY and ORDER BY, earlier versions did not support such syntax. Take care not to have the new syntax in Stored Procedures or Triggers in database files that are deployed to earlier InterBase versions. You will need InterBase 2017 Update 1 version database engine at a minimum.

Advance To:

SET DATABASE