You're reading from jOOQ Masterclass A practical guide for Java developers to write SQL queries for complex database interactions

Product type Paperback

Published in Aug 2022

Publisher Packt

ISBN-13 9781800566897

Length 764 pages

Edition 1st Edition

Languages

Java

Tools

Spring

Concepts

Database Programming

Author (1):

Anghel Leonard

View More author details

Table of Contents (26) Chapters

Preface

1. Part 1: jOOQ as a Query Builder, SQL Executor, and Code Generator

2. Chapter 1: Starting jOOQ and Spring Boot FREE CHAPTER

3. Chapter 2: Customizing the jOOQ Level of Involvement

4. Part 2: jOOQ and Queries

5. Chapter 3: jOOQ Core Concepts

6. Chapter 4: Building a DAO Layer (Evolving the Generated DAO Layer)

7. Chapter 5: Tackling Different Kinds of SELECT, INSERT, UPDATE, DELETE, and MERGE

8. Chapter 6: Tackling Different Kinds of JOINs

9. Chapter 7: Types, Converters, and Bindings

10. Chapter 8: Fetching and Mapping

11. Part 3: jOOQ and More Queries

12. Chapter 9: CRUD, Transactions, and Locking

13. Chapter 10: Exporting, Batching, Bulking, and Loading

14. Chapter 11: jOOQ Keys

15. Chapter 12: Pagination and Dynamic Queries

16. Part 4: jOOQ and Advanced SQL

17. Chapter 13: Exploiting SQL Functions

18. Chapter 14: Derived Tables, CTEs, and Views

19. Chapter 15: Calling and Creating Stored Functions and Procedures

20. Chapter 16: Tackling Aliases and SQL Templating

21. Chapter 17: Multitenancy in jOOQ

22. Part 5: Fine-tuning jOOQ, Logging, and Testing

23. Chapter 18: jOOQ SPI (Providers and Listeners)

24. Chapter 19: Logging and Testing

25. Other Books You May Enjoy

Ordered set aggregate functions (WITHIN GROUP)

Ordered set aggregate functions allow operations on a set of rows sorted with ORDER BY via the mandatory WITHIN GROUP clause. Commonly, such functions are used for performing computations that depend on a certain row ordering. Here, we can quickly mention hypothetical set functions such as RANK(), DENSE_RANK(), PERCENT_RANK(), or CUME_DIST(), and inverse distribution functions such as PERCENTILE_CONT(), PERCENTILE_DISC(), or MODE(). A particular case is represented by LISTAGG(), which is covered at the end of this section.

Hypothetical set functions

A hypothetical set function calculates something for a hypothetical value (let's denote it as hv). In this context, DENSE_RANK() computes the rank of hv without gaps, while RANK() does the same thing but with gaps. CUME_DIST() computes the cumulative distribution of hv (the relative rank of a row from 1/n to 1), while PERCENT_RANK() computes the percent rank of hv (the relative...