A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: The following aggregate function computes the number of rows per group which satifsy the FILTER clause: This is useful for pivot style queries, where multiple aggregate values are computed in one go. For...

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: This is a bit difficult to read when you see it for the first time. Think of it as a complicated way to say … Continue reading Getting Top 1 Values Per...

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: Do this: (Depending on your dialect you may require a FROM DUAL clause, … Continue reading...

One of jOOQ’s key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises – unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn’t part of jOOQ’s feature … Continue reading...