sqlpostgresqlkotlinaggregate-functionskotlin-exposed

Select multiple values and ORDER BY using Kotlin Exposed SQL DSL

Im trying to make the next query making use of Kotlin SQL Exposed framework.

SELECT interation_id, group_id, max(version)
FROM interaction
GROUP BY group_id;

I have a table of interactions, and each interaction group can have multiple interactions, each one marked with a different version. I want to retrieve the Interaction ID of each group with their latest version

Using exposed my query looks something like:

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

yet I get an error of UserTable.id must appear in the GROUP BY clause or be used in an aggregate function. But if i do this my group by will not return a single value for versionGroupId.

Extra input: An example of what i would like to reproduce would be from this table.

Interaction_id group_id version
0 1 0
1 2 0
2 2 1
3 2 2
4 1 1
5 1 2
6 2 3

I would like to get has a result Ids: 5 and 6. Those are the last version of each group ID.

Interaction_id group_id version
3 1 2
6 2 3

Anyone that have make use of kotlin Exposed SDK(or maybe is a SQL exposed trouble on my side) can give me a hand? Thanks!

Solution

  • You have to create a subquery to find the maximum version for each group in the InteractionTable and then joined this subquery with the original table to fetch the interaction_id for each group corresponding to the maximum version.

    Try this

         val MaxVersionPerGroup = InteractionTable
    .slice(InteractionTable.versionGroupID, InteractionTable.version.max())
    .selectAll()
    .groupBy(InteractionTable.versionGroupID)
    .alias("MaxVersionPerGroup")

val maxVersionColumn = MaxVersionPerGroup[InteractionTable.version.max()]

val query = InteractionTable
    .join(MaxVersionPerGroup, JoinType.INNER, additionalConstraint = { 
        InteractionTable.versionGroupID eq MaxVersionPerGroup[InteractionTable.versionGroupID] and
        InteractionTable.version eq maxVersionColumn
    })
    .slice(InteractionTable.interation_id, InteractionTable.group_id, InteractionTable.version)
    .selectAll()

query.forEach { 
    println("Interaction ID: ${it[InteractionTable.interation_id]}, Group ID: ${it[InteractionTable.group_id]}, Version: ${it[InteractionTable.version]}")
}