Select multiple values and ORDER BY using Kotlin Exposed SQL DSL

Issue

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]}")
}

Answered By - TSCAmerica.com