Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select Query on BUT000

Former Member
0 Kudos

HI All,

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

3 REPLIES 3

Former Member
0 Kudos

hi,

select max( PARTNER ) into V_PARTNER from BUT000

where PARTNER in S_PARTNER

and BU_GROUP = <value>.

if u pass range of partners into S_PARTNER, it will get the max out of that range.

regards,

madhu

Former Member
0 Kudos

Hi,

try this:


data c_max_partner like but000-partner.
select
  max( distinct partner )
into
  c_max_partner
from
  but000
where
  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.

Regards,

Jaime

Former Member
0 Kudos

Hello,

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:

data:

max_partner type bu_partner,

max_partner_grp type bu_group.

select partner bu_group into (max_partner, max_partner_grp)

from but000

where partner = ( select max( partner ) from but000 ).

endselect.

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.

Regards,

Mark M