We’ve just added support for the `EVERY()`

aggregate function (#1391) to jOOQ, and would like to take the opportunity of letting you know of this true SQL gem that can come in handy `EVERY(now and then)`

(pun intended).

Let’s assume we have four books in our table:

INSERT INTO book VALUES (1, 1, '1984'); INSERT INTO book VALUES (2, 1, 'Animal Farm'); INSERT INTO book VALUES (3, 2, 'O Alquimista'); INSERT INTO book VALUES (4, 2, 'Brida');

Now the question is:

Is

`EVERY()`

ID lower than 10?

We’ll ask:

SELECT EVERY(id < 10) FROM book

And the answer is:

every ----- true

Does

`EVERY()`

book for each author end with the letter ‘a’?

We’ll ask:

SELECT author_id, EVERY(title LIKE '%a') FROM book GROUP BY author_id

And the answer is:

author_id every ----------------- 1 false 2 true

Wonderful!

As with all aggregate functions, we can even use them as a window function!

SELECT book.*, EVERY(title LIKE '%a') OVER (PARTITION BY author_id) FROM book

Which will produce

id author_id title every ------------------------------------ 1 1 1984 false 2 1 Animal Farm false 3 2 O Alquimista true 4 2 Brida true

## Who supports `EVERY()`

Well, the SQL standard has it:

10.9 <aggregate function> <aggregate function> ::= COUNT <left paren> <asterisk> <right paren> [ <filter clause> ] | <general set function> [ <filter clause> ] | <binary set function> [ <filter clause> ] | <ordered set function> [ <filter clause> ] | <array aggregate function> [ <filter clause> ] <general set function> ::= <set function type> <left paren> [ <set quantifier> ] <value expression> <right paren> <set function type> ::= <computational operation> <computational operation> ::= AVG | MAX | MIN | SUM| EVERY <-- yes, here! EVERY!| ANY | SOME | COUNT | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | COLLECT | FUSION | INTERSECTION

But if your database is not PostgreSQL, don’t worry. `EVERY()`

can be emulated on `EVERY()`

database using `SUM()`

and `CASE`

expressions. Here’s how to emulate the first query:

-- SELECT EVERY(id < 10) -- FROM book SELECT MIN(CASE WHEN id < 10 THEN 1 ELSE 0 END) FROM book;

Or as window functions

-- SELECT -- book.*, -- EVERY(title LIKE '%a') OVER (PARTITION BY author_id) -- FROM book SELECT book.*, MIN(CASE WHEN title LIKE '%a' THEN 1 ELSE 0 END) OVER(PARTITION BY author_id) FROM book;

**For a more concise emulation, see Stew Ashton’s comments**

And, as always on this blog, we’re happy to conclude that the upcoming jOOQ 3.6 will now handle `EVERY(emulation)`

for you, so you can write:

DSL.using(configuration) .select(BOOK.fields()) .select(every(BOOK.TITLE.like("%a")) .over(partitionBy(BOOK.AUTHOR_ID))) .from(BOOK) .fetch();

Have fun with this new function!

A more concise emulation:

Sorry, I meant the following:

That’s a cool idea, thanks for sharing, Stew!

I ended up implementing this as an improvement:

I’ll also fix the article, because the original solution was not correctly aggregating empty sets to NULL