Getting top 1 per group values is a bit tricky in SQL in general. This post explains how to do this with Oracle when multiple values per group are requested

jOOQ is a Java library that provides a fluent API for building type-safe SQL queries and interacting with relational databases. With jOOQ, developers can write SQL queries in Java code, leveraging compile-time type checking and code generation to ensure correctness and maintainability. Developers can learn how to use jOOQ to simplify database interactions, reduce SQL boilerplate, and enhance productivity when working with SQL databases in Java applications.

JOOQ

Learn how to get multiple values in a top 1 per group query using Oracle's 'KEEP' syntax or the 'FIRST' syntax. Discover the use of 'ANY_VALUE()' and 'MAX()' functions for handling ties in the ordering.

Oracle's way to get multiple values in a top 1 per group query