One of MySQL 8's biggest improvements is the support of window functions. As I always said in conferences, there's SQL before window functions and SQL after window functions. Once you start using...
https://blog.jooq.org/emulating-window-functions-in-mysql-5-7/
For SQL beginners, there's a bit of an esoteric syntax named PARTITION BY, which appears all over the place in SQL. It always has a similar meaning, though in quite different contexts. The meanin...
https://blog.jooq.org/various-meanings-of-sqls-partition-by-syntax/
When paginating results in SQL, we use standard SQL OFFSET .. FETCH or a vendor specific version of it, such as LIMIT .. OFFSET. For example: SELECT first_name, last_name FROM actor ORDER BY acto...
https://blog.jooq.org/calculating-pagination-metadata-without-extra-roundtrips-in-sql/
So, @rotnroll666 nerd sniped me again. Apparently, the Neo4j Cypher query language supports arbitrary reductions, just like any functional collection API, oh say, the JDK Stream API: Stream.of(2,...
https://blog.jooq.org/implementing-a-generic-reduce-aggregate-function-with-sql/
A nice little gem in PostgreSQL's SQL syntax is the DISTINCT ON clause, which is as powerful as it is esoteric. In a previous post, we've blogged about some caveats to think of when DISTINCT and ...
https://blog.jooq.org/using-distinct-on-in-non-postgresql-databases/
I found a very interesting SQL question on Twitter recently: https://twitter.com/vikkiarul/status/1120669222672261120 Rephrasing the question: We have a set of sparse data points: +------------+-...
https://blog.jooq.org/using-ignore-nulls-with-sql-window-functions-to-fill-gaps/
A fun report to write is to calculate a cumulative percentage. For example, when querying the Sakila database, we might want to calculate the percentage of our total revenue at any given date. Th...
https://blog.jooq.org/how-to-calculate-a-cumulative-percentage-in-sql/
In my previous article, I showed what the very useful percentile functions (also known as inverse distribution functions) can be used for. Unfortunately, these functions are not ubiquitously avai...
https://blog.jooq.org/how-to-emulate-percentile_disc-in-mysql-and-other-rdbms/
All SQL databases support the standard aggregate functions COUNT(), SUM(), AVG(), MIN(), MAX(). Some databases support other aggregate functions, like: EVERY() STDDEV_POP() STDDEV_SAMP() VAR_POP(...
https://blog.jooq.org/writing-custom-aggregate-functions-in-sql/
SQL is a verbose language, and one of the most verbose features are window functions. In a stack overflow question that I've encountered recently, someone asked to calculate the difference betwee...
https://blog.jooq.org/how-to-reduce-syntactic-overhead-using-the-sql-window-clause/
I've stumbled across this fun SQL question on reddit, recently. The question was looking at a time series of data points where some events happened. For each event, we have the start time and the...
https://blog.jooq.org/find-the-next-non-null-row-in-a-series-with-sql/
Exciting discovery when playing around with PostgreSQL 11! New SQL standard window function clauses have been supported. If you want to play with this, you can do so very easily using docker: doc...
Imagine you have a configuration table like the following: CREATE TABLE rule ( name VARCHAR2(50) NOT NULL PRIMARY KEY, enabled NUMBER(1) DEFAULT 1 NOT NULL CHECK (enabled IN (0,1)), priority NUMB...
https://blog.jooq.org/using-unpivot-to-traverse-a-configuration-tables-rows-and-columns/
Usually, this blog is 100% pro window functions and advocates using them at any occasion. But like any tool, window functions come at a price and we must carefully evaluate if that's a price we'r...
https://blog.jooq.org/how-to-avoid-excessive-sorts-in-window-functions/
In recent months, there had been some really exciting news from the MySQL team: (Recursive) Common Table Expressions in MySQL Introducing Window Functions These two SQL standard language features...
https://blog.jooq.org/jooq-3-10-supports-exciting-mysql-8-0-features/
I'm frequently telling developers to put window functions almost everywhere, because they're so awesome! One feature that I rarely see in the wild (even if it is extremely useful for reporting) i...
https://blog.jooq.org/a-little-known-sql-feature-use-logical-windowing-to-aggregate-sliding-ranges/
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month (today, exceptionally on a Wednesday because of technical issues) where we i...
Listicles like these do work - not only do they attract attention, if the content is also valuable (and in this case it is, trust me), the article format can be extremely entertaining. This artic...
https://blog.jooq.org/10-sql-tricks-that-you-didnt-think-were-possible/
This Stack Overflow question has yet again nerd-sniped me maximum element in the array that would result from performing all M operations Here's the question by John that was looking for a Java ...
https://blog.jooq.org/time-for-some-funky-sql-prefix-sum-calculation/
Recently, we've published our article about the awesome window function support in jOOλ 0.9.9, which I believe is some of the best additions to the library that we've ever done. Today, we'll loo...
https://blog.jooq.org/how-to-pattern-match-files-and-display-adjacent-lines-in-java/
You heard right. Up until now, the awesome window functions were a feature uniquely reserved to SQL. Even sophisticated functional programming languages still seem to lack this beautiful function...
https://blog.jooq.org/2016-will-be-the-year-remembered-as-when-java-finally-had-window-functions/
The following is a very common problem in all data related technologies and we're going to look into two very lean, SQL-based solutions for it: How do I fill the cells of a sparse data set with t...
https://blog.jooq.org/how-to-fill-sparse-data-with-the-previous-non-empty-value-in-sql/
A very interesting problem that can be solved very easily with SQL is to find consecutive series of events in a time series. But what is a consecutive series of events in a time series? Take Stac...
https://blog.jooq.org/how-to-find-the-longest-consecutive-series-of-events-in-sql/
I've stumbled upon this very interesting question on Stack Overflow, recently. Its title is: compare a number with sum of subset of numbers In this article, we'll compare the user's imperative a...
https://blog.jooq.org/how-to-find-the-closest-subset-sum-with-sql/
Whenever you feel that itch... Can't I calculate this with SQL? The answer is: Yes you can! And you should! Let's see how... Calculating time differences between rows Let's consider the following...
We've been blogging about Java and SQL for a while now, on the jOOQ blog. Over the years, while researching interesting blog topics, we've discovered a lot of SQL gems in the blogosphere that hav...
We've been blogging a lot about the merits of modern SQL on the jOOQ blog. Specifically, window functions are one of the most fascinating features. But there are many many others. Markus Winand, ...
https://blog.jooq.org/still-using-windows-3-1-so-why-stick-to-sql-92/
There are some things in SQL that we simply take for granted without thinking about them properly. One of these things are the GROUP BY and the less popular HAVING clauses. Let's look at a simple...
https://blog.jooq.org/do-you-really-understand-sqls-group-by-and-having-clauses/
If you're using a commercial database or PostgreSQL / Firebird / CUBRID, you will be able to take advantage of the full power of window functions. We've blogged about window functions' awesomenes...
https://blog.jooq.org/dont-miss-out-on-awesome-sql-power-with-first_value-last_value-lead-and-lag/
One of the best features in SQL are window functions. Dimitri Fontaine put it bluntly: There was SQL before window functions and SQL after window functions If you're lucky enough to be using any ...
https://blog.jooq.org/the-difference-between-row_number-rank-and-dense_rank/
(Sorry for that click-bait heading. Couldn't resist ;-) ) We're on a mission. To teach you SQL. But mostly, we want to teach you how to appreciate SQL. You'll love it! Getting SQL right or wrong ...
If anything at all, our jOOQ talks at various JUGs and conferences have revealed mostly one thing: Java developers don't know SQL. And it isn't even necessarily our fault. We're just not exposed ...
https://blog.jooq.org/nosql-no-sql-how-to-calculate-running-totals/
Subscribe to this newsletter here Tweet of the Day Our customers, users, and followers are sharing their love for jOOQ to the world. Here are: Mahmud who cannot wait to make more magic with jOOQ...
https://blog.jooq.org/jooq-newsletter-april-16-2014-monthly-yearly-perpetual-licenses-now-available/
Haven't we all been wondering: How can I do this? I have these data in Excel and I want to group / sort / assign / combine ... While you could probably pull up a Visual Basic script doing the wor...
When doing reporting or statistics with SQL, you better know your window functions. There are many of them, and few SQL developers know about them. CUME_DIST() is one such function. We've recentl...
Once you get a hang of the very peculiar syntax, SQL is a highly expressive and rich language offering incredible features at a declarative level. One of the coolest features are window functions...
https://blog.jooq.org/probably-the-coolest-sql-feature-window-functions/