CODEWITHSUNDEEP
scalaslick
joesan
joesan

Reputation: 15435

Slick 2.1.0 GroupBy and fetch all columns

For the following table structure:

id externalId name version
1  10         n1   1
2  65         n2   2
3  10         n3   2
4  77         n4   1

I'm trying to get all the entries (all columns) that has a max version grouped by externalId. The expected result should be:

id externalId name version
2  65         n2   2
3  10         n3   2
4  77         n4   1

For this purpose, I have the following slick query defined:

val resulting = myTableEntries
 .groupBy(x => x.externalID)
 .map { 
   case (id, group) => 
     (id, group.map(_.version).max) 
 }

How can I get all the columns out instead of just the id and the version?

Effectively what I need is a Slick version of the following SQL:

select myTable.id, myTable.name, myTable.externalId, myTable.version
    from MyTable myTable
    where version = 
        (select max(revision) from MyTable myTable1 where myTable.id=myTable1.id)

Upvotes: 2

Views: 726

Answers (2)

Gábor Bakos
Gábor Bakos

Reputation: 9100

Based on this example and this answer, I think you want the following:

val maxVersionByExternalID = myTableEntries
 .groupBy(x => x.externalID)
 .map { 
   case (externalID, group) => 
     (externalID, group.map(_.version).max)
//We have the pair of ids and max versions _not yet run_ so we can join
val resulting = (myTableEntries join maxVersionByExternalID on (
    (entries, maxPair) => //join condition
       entries.externalID === maxPair._1 && entries.version === maxPair._2))
  .map{case (entries, maxPair) => entries}//keep only the original
  .run //materialize at the end

Upvotes: 2

Akash
Akash

Reputation: 411

I have not worked on Slick, but looking at the scala code and your requirement, the below mentioned question comes to my mind.

The other way to solve your problem is using Rank function in SQL, give rank to every record within group, in your case externalID and rank over version in your case.

Once you are done with that you can select all the records with rank as 1

Please refer the below question on how to solve such cases.

Spark two level aggregation

Upvotes: 0

Related Questions