Question Re: CAP using subqueries for case separation in Technology Q&A

CAP using subqueries for case separation

a_oezsoy59 — Tue, 10 Dec 2024 08:13:01 GMT

Hi,

From the following table I am trying to get the the records grouped by RefA and RefB columns with the maximum value of Value1. In case the Value1 is same for multiple records, then the maximum value of Value2.

So only in this example the records 1 and 5.

ID	RefA	RefB	Value1	Value2
1	a1	b1	5	10
2	a1	b1	3	15
3	a1	b1	3	5
4	a2	b1	4	25
5	a2	b1	4	35

Ideally I would like to do this operation with only one query and no server-side post processing.

In SQL the following query gets me the correct result.

-- Option 1: Subqueries for SQLite SELECT a.RefA, a.RefB, MAX(a.Value1) AS max1, MAX( CASE WHEN a.Value1 = subquery.max1 THEN a.Value2 ELSE NULL END ) AS max2 FROM table1 as a JOIN ( SELECT RefA, RefB, MAX(Value1) AS max1 FROM table1 GROUP BY RefA, RefB ) AS subquery ON a.RefA = subquery.RefA AND a.RefB = subquery.RefB GROUP BY a.RefA, a.RefB; -- Option 2: Window function in case separation (general) SELECT RefA, RefB, MAX(Value1) AS max1, MAX( CASE WHEN Value1 = MAX(Value1) OVER( PARTITION BY RefA, RefB ) THEN Value2 ELSE null END ) AS max2 FROM table1 GROUP BY RefA, RefB;

However, I don't know what the correct approach for this is in CAP. Can you help me with this?

Thanks in advance.

Atakan

Re: CAP using subqueries for case separation

catano — Thu, 12 Dec 2024 07:44:02 GMT

Hi @a_oezsoy59 ,

How about defining a new entity with the as select from variant as described in the documentation?
Further reference for functions:

The syntax is slightly different, but the query in your example could be translated into such select I believe.
If you already defined the entities - e.g. to table1 -, the "Subqueries for SQLite" query will look like this:

/* Option 1: Subqueries */ entity AggregateView as select from table1 as a join ( select from table1 { RefA, RefB, max(Value1) as max1, } group by RefA, RefB ) as subquery on a.RefA = subquery.RefA and a.RefB = subquery.RefB { a.RefA, a.RefB, max( a.Value1 ) as max1, max( case when a.Value1 = subquery.max1 then a.Value2 else null end ) as max2 } group by a.RefA, a.RefB;

Best,
Peter

Re: CAP using subqueries for case separation

a_oezsoy59 — Mon, 16 Dec 2024 07:48:16 GMT

Hi Peter,

Thank you! That was exactly what I was looking for 🙂

Re: CAP using subqueries for case separation

catano — Mon, 16 Dec 2024 08:16:41 GMT

I'm glad it worked