JOOQ query to get the latest record of each user

Question

I have created a dirty way of doing this which works but wondering if there's a simpler way I can obtain this. Basically I just want to get the latest user tool id's finished for each user. Thanks in advance!

var allUserTools = masterDB.select(Tool.USER_ID, Tool.USER_TOOL_ID)
                    .from(Tool.TABLE)
                    .orderBy(Tool.FINISHED.desc())
                    .fetchGroups(Tool.USER_ID, UserTool.USER_TOOL_ID));


    List<Integer> allUserToolIDs = new ArrayList<>();
    for (Map.Entry<Integer, List<Integer>> userTool : allUserTools.entrySet()) {
        if(!userTool.getValue().isEmpty()) {
            allUserToolIDs.add(userTool.getValue().get(0));
        }
    }

Answer 1

This is an example of a TOP N per category query, and more specifically, since you're only interested in the "TOP 1" row per category (or group), there's a simple solution for PostgreSQL, using DISTINCT ON:

SELECT DISTINCT ON (USER_ID), USER_ID, USER_TOOL_ID
FROM TOOL
ORDER BY USER_ID, FINISHED DESC

This can be read as getting only distinct values for the (USER_ID, USER_TOOL_ID) tuple, preferring the first one according to the ORDER BY clause. It's a rather esoteric syntax for something that could also be done with window functions, more verbosely (see the manual page).

In jOOQ:

masterDB.select(TOOL.USER_ID, TOOL.USER_TOOL_ID)
        .distinctOn(TOOL.USER_ID)
        .from(TOOL)
        .orderBy(TOOL.USER_ID, TOOL.USER_TOOL_ID, TOOL.FINISHED.desc())
        .fetch();