One of the more frequent questions about jOOQ is how to write a derived table (or a CTE). The jOOQ manual shows a simple example of a derived table: In SQL: SELECT nested.* FROM ( SELECT AUTHOR_ID, count(*) books FROM BOOK GROUP BY AUTHOR_ID ) nested ORDER BY nested.books DESC In jOOQ: // Declare the … Continue reading How to Write a Derived Table in jOOQ
Tag: cte
The SQL Language’s Most Missing Feature
SQL is also awesome in many ways. We can write out the most complex truths and facts and have the database tell us the answer in no time. But the SQL language is arguably the most beautiful programming language out there. It has so many caveats that people like me get ridiculously rich selling consulting services … Continue reading The SQL Language’s Most Missing Feature
Recursive SQL for Data Normalisation
Recursive SQL can be awesome, although a bit hard to read in its SQL standard beauty. Let's assume you have some aggregated data with dates and a number of events per date: | DATE | COUNT | |--------------------------------|-------| | October, 01 2013 00:00:00+0000 | 2 | | October, 02 2013 00:00:00+0000 | 1 | | … Continue reading Recursive SQL for Data Normalisation
PostgreSQL Syntax is a Mystery Only Exceeded by its Power
I just ran across this rather powerful PostgreSQL example statement from the manual. It reads Increment the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table: WITH upd AS ( UPDATE employees SET sales_count = sales_count + 1 … Continue reading PostgreSQL Syntax is a Mystery Only Exceeded by its Power
Emulating the SQL standard derived column list
Derived column lists are a fine feature, if your database supports them. The SQL:2008 standard specifies 7.6 <table reference> <table reference> ::= <table factor> | <joined table> <table factor> ::= <table primary> [ <sample clause> ] <table primary> ::= <table or query name> [ [ AS ] <correlation name> [ <left paren> <derived column list> … Continue reading Emulating the SQL standard derived column list