Get the complete row with maximum column value grouped by other Column

Hello .. I have a table that I want to get the maximum value of D grouped by A & C . but also I want to get B that related to the Max value of D.

This is an example of the table

      |A|   |B|   |C|   |D|
       1     a     x     2  -->
       1     b     x     1
       1     c     y     6  -->
       1     a     y     5
       2     b     x     1
       2     c     x     6  -->
       2     a     y     5  -->
       2     b     y     1

I have tried grpstats

grpstats(Tbale, {'A','C'}, {'max'} ,'DataVars',{'D'});

but It I couldn't find a way to get the B value

The expect result is

      |A|   |B|   |C|   |D|
       1     a     x     2  
       1     c     y     6  
       2     c     x     6  
       2     a     y     5

[Azzi Abdelmalek]

What do you mean by the maximum value of D grouped by A & C? Post the expected result

[SaraJr]

I have marked the expected results in the example(-->)and added it at the end of the question ..

Answer 1

[Stephen Jue]

If I understand correctly, you want to filter your table such that it only shows the rows with max values of "D" grouped by "A" and "C".

I don't think that "grpstats" can retrieve the whole table, but you can take a more manual approach by creating your own logical filters and making a new table by taking the max of each set of rows. Here is how I did it:

% Reproduce your example table
A = [ones(4,1); 2 * ones(4,1)];
B = ('abcabcab')';
C = ('xxyyxxyy')';
D = [2,1,6,5,1,6,5,1]';
t = table(A,B,C,D);

colAValues = unique(t.A); % All unique values of column A
colCValues = unique(t.C); % All unique values of column C

tableFilter = @(a, c) (t.A == a) & (t.C == c); % Group by A and C
columnCombos = combvec(colCValues', colAValues'); % All combinations of A and C
logicalFilters = arrayfun(tableFilter, columnCombos(2,:), columnCombos(1,:), 'UniformOutput', false);

tNew = table;
for i = 1:length(logicalFilters)
    a = t(logicalFilters{i}, :);
    tNew(i, :) = a(a.D == max(a.D), :);
end

Once this code runs, "tNew" should contain the rows in your expected result.