I've blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: SELECT max(actor_id) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(first_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(last_name) KEEP (DENSE_RANK FIRST ORDER … Continue reading Getting Top 1 Values Per Group in Oracle
Tag: Oracle
An Efficient Way to Check for Existence of Multiple Values in SQL
In a previous blog post, we've advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL. I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of: SELECT count(*) FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = … Continue reading An Efficient Way to Check for Existence of Multiple Values in SQL
LATERAL is Your Friend to Create Local Column Variables in SQL
The standard SQL WITH clause has been tremendously helpful in structuring SQL queries. Instead of nesting everything in unreadable derived tables like this: SELECT actor_id, name, COUNT(*) FROM ( SELECT actor_id, first_name || ' ' || last_name AS name FROM actor ) AS a JOIN film_actor AS fa USING (actor_id) GROUP BY actor_id, name ORDER … Continue reading LATERAL is Your Friend to Create Local Column Variables in SQL
Calling Procedures with Default Parameters using JDBC or jOOQ
Using jOOQ's code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure: CREATE OR REPLACE PROCEDURE p ( p_i1 IN number, p_o1 OUT number, p_i2 IN varchar2, p_o2 OUT varchar2 ) IS BEGIN p_o1 := p_i1; p_o2 := p_i2; … Continue reading Calling Procedures with Default Parameters using JDBC or jOOQ
The Best Way to Call Stored Procedures from Java: With jOOQ
jOOQ is mainly known for its powerful type safe, embedded, dynamic SQL capabilities that are made available through code generation. However, a secondary use case of code generation is to use it for stored procedures (possibly exclusively for stored procedures). Stored procedures are powerful ways of moving complex data processing logic to the server. This … Continue reading The Best Way to Call Stored Procedures from Java: With jOOQ
The Performance of Various To-Many Nesting Algorithms
It's been a while since jOOQ 3.15 has been released with its revolutionary standard SQL MULTISET emulation feature. A thing that has been long overdue and which I promised on twitter a few times is to run a few benchmarks comparing the performance of various approaches to nesting to-many relationships with jOOQ. This article will … Continue reading The Performance of Various To-Many Nesting Algorithms
Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
An interesting hint by Vladimir Sitnikov has made me think about a new benchmark for jOOQ: https://twitter.com/lukaseder/status/1407662449331949568 The benchmark should check whether single row queries should have a JDBC Statement.setFetchSize(1) call made to them by default. The Javadoc of the method says: Gives the JDBC driver a hint as to the number of rows that … Continue reading Setting the JDBC Statement.setFetchSize() to 1 for Single Row Queries
Projecting Type Safe Nested TableRecords with jOOQ 3.17
A long standing feature request has seen little love from the jOOQ community, despite a lot of people probably wanting it. It goes by the unimpressive title Let Table<R> extend SelectField<R>: https://github.com/jOOQ/jOOQ/issues/4727 What does the feature mean, specifically? The awesome PostgreSQL Let's have a look at a really cool PostgreSQL feature. In PostgreSQL, it is … Continue reading Projecting Type Safe Nested TableRecords with jOOQ 3.17
Vendor Agnostic, Dynamic Procedural Logic with jOOQ
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, … Continue reading Vendor Agnostic, Dynamic Procedural Logic with jOOQ
jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL
This is how SQL should have been used all along. They called it The Third Manifesto, ORDBMS, or other things. Regrettably, it never really took off. Because most vendors didn't adopt it. And those who did, didn't agree on syntax. But this is about to change. Thanks to the now ubiquitous SQL/JSON support (which jOOQ … Continue reading jOOQ 3.15’s New Multiset Operator Will Change How You Think About SQL