PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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!

Tags:

2

Answers


  1. 0

    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 query = InteractionTable
        .join(MaxVersionPerGroup, JoinType.INNER, additionalConstraint = { 
            InteractionTable.versionGroupID eq MaxVersionPerGroup[InteractionTable.versionGroupID] and
            InteractionTable.version eq MaxVersionPerGroup[InteractionTable.version.max()]
        })
        .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]}")
    }
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search