EVERY

Purpose

This function returns TRUE if the value of expr is true for all rows in the window or group of input rows. Otherwise, this function returns FALSE.

Syntax

every::=

Usage Notes

  • This function returns NULL, if the window of rows is empty.
  • DISTINCT has no effect.

Examples

Aggregate Function

Copy 
SELECT 
    department, 
    EVERY(age >= 30) EVERY
FROM employee_table GROUP BY department;
Result
DEPARTMENT EVERY
ACCOUNTS false
HR true

Analytic Function

Copy 
SELECT 
    id, department, age, 
    EVERY(age >= 30) OVER (PARTITION BY department ORDER BY age) EVERY 
FROM employee_table ORDER BY department, age;
Result
ID DEPARTMENT AGE EVERY
2003 ACCOUNTS 27 false
2001 ACCOUNTS 30 false
2001 ACCOUNTS 32 false
2004 ACCOUNTS 42 false
2005 ACCOUNTS 42 false
1001 HR 30 true
1004 HR 30 true
1002 HR 34 true
1003 HR 40 true