Select multiple values and ORDER BY using Kotlin Exposed SQL DSL

Question

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!

Answer 1

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

Answer 2

Postgresql specific DISTINCT ON SQL query:

SELECT DISTINCT ON (group_id) interation_id, group_id, version
FROM interaction
ORDER BY group_id, version DESC;

Generic window function SQL query:

SELECT interation_id, group_id, version
FROM
(
    SELECT interation_id, group_id, version,
           ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY version DESC) rn
    FROM interaction
) dt
WHERE rn = 1

Answer 3

Based on @TSCAmerica answer based on the DSL version you may need to use an alias over the version.max() reference. It may look like:

val maxVersion = InteractionTable.version.max().alias("max_version")
val maxVersionPerGroup = InteractionTable
            .slice(ArtifactTable.versionGroupID, maxVersion)
            .selectAll()
            .groupBy(InteractionTable.versionGroupID)
            .alias("maxVersionPerGroup")

val query = InteractionTable
            .join(maxVersionPerGroup, JoinType.INNER, additionalConstraint = {
                (InteractionTable.versionGroupID eq maxVersionPerGroup[InteractionTable.versionGroupID]) and
                    (InteractionTable.version eq maxVersionPerGroup[maxVersion])
            })
            .slice(InteractionTable.id)
            .selectAll()

query.toList.forEach {
    //Use the InteractioValue
}

Answer 4

It is because query doesn't know which interID need to use. Set it max and it show max id.

 UserTable
    .slice(UserTable.interID.max(), UserTable.gropeID, UserTable.version.max())
    .selectAll()
    .groupBy(UserTable.gropeID)
    .map {
        println("${it[UserTable.interID.max()]} ${it[UserTable.gropeID]} ${it[UserTable.version.max()]}")
    }