We have a requirement where we need to access the highest Partner number present in table BUT000. But along with the partner number we would also like to check on another particular field called BU_GROUP (which determines the grouping of the partner). So we need to retreive the highest partner number for a particular group from table BUT000.
Can you help in writing a most effective query on selecting the Max partner number (Field - PARTNER) along with a particular grouping (Field - BU_GROUP).
Any help in this regard will be highly appreciated.
Warm Regards, Vinay
data c_max_partner like but000-partner.
max( distinct partner )
bu_group = <any_group>.
There's no index for the field BU_GROUP in BUT000 so, according to the execution plan of the SQL statement, a full table scan will be used (which is the worst way to read a table). If your BUT000 table is "small" it could be acceptable to use this statement. If not, try to include some fields in the where clause so that a index could be used.
I understand your question differently from the previous repliers; what I think you want is: select the highest partner from BUT000 and then also the BU_GROUP to which that specific partner belongs. In that case you don't know BU_GROUP in advance so you cannot use it in the WHERE clause.
In ABAP you can solve this with a subquery. Following code works:
max_partner type bu_partner,
max_partner_grp type bu_group.
select partner bu_group into (max_partner, max_partner_grp)
where partner = ( select max( partner ) from but000 ).
This query is also efficient: the subquery uses an index scan over the primary key, and the outer query a unique get also via the primary key. It is not necessary to handle the case of the select returning multiple rows because PARTNER is unique.