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