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 actor_id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY As always, we're using the Sakila database for this example. This is rather … Continue reading Calculating Pagination Metadata Without Extra Roundtrips in SQL
Tag: OFFSET pagination
Why Most Programmers Get Pagination Wrong
Pagination is one of those things that almost everyone gets wrong for two reasons: User experience Database performance Here's why. What's wrong with pagination? Most applications blindly produce pagination like this: This is how GMail implements pagination. With my current settings, it displays 100 E-Mails at a time and also shows how many E-Mails there … Continue reading Why Most Programmers Get Pagination Wrong
jOOQ Tuesdays: Markus Winand is on a Modern SQL Mission
Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. This includes people who work with SQL, Java, Open Source, and a variety of other related topics. We are excited to … Continue reading jOOQ Tuesdays: Markus Winand is on a Modern SQL Mission
10 SQL Articles Everyone Must Read
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 have inspired our work and our passion for SQL. Today, we're presenting to you a list of 10 articles that we … Continue reading 10 SQL Articles Everyone Must Read
jOOQ Newsletter: August 15, 2014 – jOOQ 3.5 Outlook
Subscribe to this newsletter here jOOQ 3.5 Outlook We're working hard on the next release. Already 90 issues for jOOQ 3.5 are closed and counting! Today, we're going to look at the highlights of what will be implemented in the next, exciting minor release, due for Q4 2014: Support for new databases Our customers have … Continue reading jOOQ Newsletter: August 15, 2014 – jOOQ 3.5 Outlook
Join the No OFFSET Movement!
Markus Winand from Use The Index, Luke! did it again. He started an exciting battle against one the biggest flaws in the SQL language: We've blogged about this before. OFFSET pagination is terribly slow, once you reach higher page numbers. Besides, chances are, that your database doesn't even implement it correctly, yet (and your emulation … Continue reading Join the No OFFSET Movement!
jOOQ Newsletter: June 18, 2014 – jOOQ goes JavaOne™
jOOQ Goes JavaOne™ We're super excited to announce that our CEO and Head of R&D Lukas will be heading to San Francisco this fall to talk about jOOQ at JavaOne™! This is not just great for Data Geekery and jOOQ, but also for the whole Java / SQL ecosystem, as we believe that the Java / SQL … Continue reading jOOQ Newsletter: June 18, 2014 – jOOQ goes JavaOne™
SQL Server Trick: Circumvent Missing ORDER BY Clause
SQL Server is known to have a very strict interpretation of the SQL standard. For instance, the following expressions or statements are not possible in SQL Server: -- Get arbitrarily numbered row_numbers SELECT ROW_NUMBER() OVER () -- Skip arbitrary rows SELECT a FROM (VALUES (1), (2), (3), (4)) t(a) OFFSET 3 ROWS Strictly speaking, that … Continue reading SQL Server Trick: Circumvent Missing ORDER BY Clause