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));
}
}
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();