SELECT Major record

f_allocca — Mon, 02 Mar 2020 18:16:42 GMT

in a ZTAB, for the same ZFIELD1 field that is not a key field, I have to take the record with the higher ZFIELD2, how can I do it?

Re: SELECT Major record

BiberM — Mon, 02 Mar 2020 19:19:09 GMT

For a single select you can filter for ZFIELD1 and then order by ZFIELD2 descending:

select *
from ztab
where zfield1 eq 'ABC'
order by zfield2 descending
into @data(select_result_single).
endselect.

For a mass selection you can group by ZFIELD1 and select the maximum ZFIELD2 if you are only interested in these fields:

select zfield1,
       max(zfield2)
from ztab
group by zfield1
into table @data(select_result_multiple).

If you need the whole line you will have to use above select as preselection and select the whole record afterwards (best: join, also possible: sub-select). This could the also be hidden inside cds-views.
This is often needed when needing to select the most up to data record for every key combination.

topic SELECT Major record in Application Development and Automation Discussions

SELECT Major record

Re: SELECT Major record