Greatest N per group in Open SQL

Question

Selecting the rows from a table by (partial) key with the maximum value in a particular column is a common task in SQL. This question has some excellent answers that cover a variety of approaches to it. Unfortunately I'm struggling to replicate this in my ABAP program.

None of the commonly used approaches seem to be supported:

Joining on a subquery is not supported in syntax: SELECT * FROM X as x INNER JOIN ( SELECT ... ) AS y
Using IN for a composite key is not supported in syntax as far as I know: SELECT * FROM X WHERE (key1, key2) IN ( SELECT key1 key2 FROM ... )
Left join to itself with smaller-than comparison is not supported, outer joins only support EQ comparisons: SELECT * FROM X AS x LEFT JOIN X as xmax ON x-key1 = xmax-key1 AND x-key2 < xmax-key2 WHERE xmax-key IS INITIAL

After trying each of these solutions in turn only to discover that ABAP doesn't seem to support them and being unable to find any equivalents I'm starting to think that I'll have no choice but to dump the data of the subquery to an itab.

What is the best practice for this common programming requirement in ABAP development?

Do you have a reference table for the first part of the key that you could use? — vwegert
@vwegert Do you mean that I should encapsulate the composite key inside a structure? I've only seen reference tables referred to as being used to clarify currencies on value columns. — Lilienthal

Zero Zero · Accepted Answer · 2018-05-29T09:59:39

First of all, specific requirement, would give you a better answer. As it happens I bumped into this question when working on a program, that uses 3 distinct methods of pseudo-grouping, (while looking for alternatives) and ALL 3 can be used to answer your question, depending on what exactly you need to do. I'm sure there are more ways to do it.

For instance, you can pull maximum values within a group by simply selecting max( your_field ) and grouping by some fields, if that's all you need.

select bname, nation, max( date_from ) from adrp group by bname, nation. "selects highest "from" date for each bname

If you need to use that max value as a filter condition within a query, you can do it by performing pseudo-grouping using sub-query and max within sub-query like this (notice how I move out the BNAME check into sub query, which means I don't have to check both fields using in (subquery) addition):

select ... from adrp as b_adrp "Pulls the latest person info for a user (some conditions are missing, but this is a part of an actual query)
where b_adrp~date_from in (
    select max( date_from ) "Highest date_from where both dates are valid
         from adrp  where persnumber = b_adrp~persnumber and nation = b_adrp~nation and date_from <= @sy-datum )

The query above allows you to select selects all user info from base query and (where the first one only allows to take aggregated and grouped data).

Finally, If you need to check based on composite key and compare it to multiple agregate function results, the implementation will heavily depend on specifics of your requirement (and since your question has none, I'll provide a generic one). Easiest option is to use exists / not exists instead of in (subquery), in exact same way and form the subquery to check for existance of specific key or condition rather than pull a list ( you can nest subqueries if you have to ):

 select * from bkpf where exists ( select 1 from bkpf as b where belnr = bkpf~belnr and gjahr = bkpf~gjahr group by belnr, gjahr having max( budat ) = bkpf~budat ) "Took an available example, that I had in testing program.

All 3 queries will get you max value of a column within a group and in fact, all 3 can use joins to achieve identical results.

Greatest N per group in Open SQL

3 Answers