Hello! Im struggling a bit making use of `max` on ...

# exposed

[Durdin]

Hello! Im struggling a bit making use of max on a select with Exposed Kotlin. I wanna do something like

SELECT user_id, group_id, max(version)
FROM users
GROUP BY group_id;

In this case i want to get those version_id with the max version inside the same groupId. I dont achieve the query to work because i dont get properly the subQuery builder by using a groupBy. Atm my where clause looks like:

UserTable.slice(UserTable.id, UserTable.versionGroupID, UserTable.version.max())
                    .selectAll()
                    .groupBy(UserTable.versionGroupID)

Based on previous comments on the channel it should be something like this. However i get all time the subquery has too many columns or field must be contained on GROUP BY or used on an expression
using the kotlin Exposed DSL atm. Appreciate if someone more into the use of the sdk could give me a hand! Thanks alot!

[Chantal Loncle]

Hi @Durdin From an Exposed POV, you're using max() correctly and the DSL query generates the SQL that you mentioned wanting above.
From the database POV, the query you want is invalid because it doesn't follow the rules of a SELECT statement with a GROUP BY clause.

As explained by the exceptions you're seeing, some databases (like SQL Server, PostgreSQL, Oracle, and MySQL) have the following rule:
• All selected columns must either be in the GROUP BY clause, be an aggregate column, or somehow be functionally dependent on the column in the GROUP BY clause.
If you're using MySQL, I believe that this rule can be loosened by disabling the mode only_full_group_by or, for example, by ensuring that versionGroupID has a unique column index (if id is the primary key). Here's the MySQL official reference that covers your options.

If you're using another database, there are different queries that can be used to workaround this SQL restriction, but the best option for your use case will be dependent on the specific database.
Please consider either checking the official database reference for GROUP BY guidelines or doing a general search for how to "select column not in GROUP BY" for your specific database.