CAP using subqueries for case separation

[a_oezsoy59]

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

[catano · Accepted solution]

Hi @a_oezsoy59 ,

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

• Functions Mappings for Runtime Queries
• Using Native Features

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

[a_oezsoy59 · Accepted solution]

Hi Peter,

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

[catano · Accepted solution]

I'm glad it worked