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 ORDER BY are used together. The bigger picture can be seen in our article about the logical order of … Continue reading Using DISTINCT…
#first value
4 posts
9 Sept 2019
20 Sept 2018
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 between the first and the last value in a time series for any given day: Input volume tstamp --------------------------- 29011 2012-12-28 09:00:00 28701 2012-12-28 10:00:00 … Continue reading How to…
12 May 2015
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 database containing timestamps (e.g. in a log database). We’re using PostgreSQL syntax for this: Obviously, you’ll be adding constraints and indexes, etc. Now, … Continue reading Use this…
7 Nov 2014
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’ awesomeness a couple of times, in particular about ROW_NUMBER(), RANK(), DENSE_RANK(). Today, we’re going to look into some awesome window functions that produce values … Continue reading Don’t Miss…