How to sort table according to multiple columns at a time?

Question

I have table with 16 columns, first three are input columns. I want to sort whole table according to first three columns at a time.

T = table(a1, a2, a3, ..., a16)

All a1, a2, and a3 will be sorted in ascending order.
For example:

a1 = [6 3 9 6 3 9 6 5]' 
a2 = [7 8 2 3 7 7 6 7]' 
a3 = [9 2 3 3 4 3 7 4]'

The output shall be:

a1 = [3 3 5 6 6 6 9 9]'
a2 = [7 8 7 3 6 7 2 7]'
a3 = [4 2 4 3 7 9 3 3]'

I want to sort whole table. Actual table is quite big. Next 11 columns will be sorted according to first three. Study of input and output of first three columns is sufficient to understand. It is similar to MS Excel multi column sorting. — user9003011, Dec 26 '17 at 11:54
This is similar to MS Excel sorting according to multiple columns. Observation of first three vectors provides sufficient idea. This observation will also clarify one to understand how other columns will get sorted. — user9003011, Dec 26 '17 at 11:57

liyuan · Accepted Answer · 2017-12-28T11:43:20.947

1

I think the function you are looking for is sortrows. e.g.

a1 = [6 3 9 6 3 9 6 5]';
a2 = [7 8 2 3 7 7 6 7]';
a3 = [9 2 3 3 4 3 7 4]';
temp = table(a1,a2,a3);
sortrows(temp,[1,2,3])

where you supply a vector of the columns you want to sort by.

This gives you

ans =
  8×3 table
    a1    a2    a3
    __    __    __
    3     7     4 
    3     8     2 
    5     7     4 
    6     3     3 
    6     6     7 
    6     7     9 
    9     2     3 
    9     7     3

,sorted first by column 1, then column 2, and finally column 3.

edited Dec 28 '17 at 11:43

answered Dec 28 '17 at 01:39

liyuan

543
3
16

No, this is not expected. If you look at input and ouput. Say array a1: 3 is the most minimum in that column, but repeated ( of table rows 3, 7, 4 and 3, 8, 2). The second sorting on a2 forces 3, 7, 4 to be descending value than 3, 8, 2. Preference of a3 is of low priority, it becomes powerful when repetition in column of a2 occurs. This way it continues further.... – user9003011 Dec 28 '17 at 11:38
Ok sorry I should follow your example, I think I understand what you want, it's just that I'm giving an example that is sorting first by column 2, then column 1 then column 3, just to show that you can specify the column preference by supplying a vector of column indices – liyuan Dec 28 '17 at 11:41

score 0 · Answer 2 · answered Dec 26 '17 at 12:27

I am also not sure, if I fully understand the question. If I understand correctly, you want to sort by the first column, and if there are equal values in the first column consider the second column...

One way to solve this problem (assuming positive values only) would be to exploit the range of int/float/double like this:

a1 = [6 3 9 6 3 9 6 5]';
a2 = [7 8 2 3 7 7 6 7]';
a3 = [9 2 3 3 4 3 7 4]';
[~,order] = sort((a1 * max(a2+1) + a2 ) * max(a3+1) + a3);

This would give you the desired result:

[a1(order)'; a2(order)'; a3(order)']
 3     3     5     6     6     6     9     9
 7     8     7     3     6     7     2     7
 4     2     4     3     7     9     3     3

Using max(abs(...)) would problably also allow you to use negative values. This approach however is pragmatic but not so nice, I think...

Columnwise sorting is preferential sorting. 1st preference to column a1, 2nd to a2, 3rd to a3 and so on. For better understanding of problem. I suggest you to try MS Excel or Libre office spreadsheet. Repeat values may or may not be present in this table. Irrespective of repetition, sorting need to occur. — user9003011, Dec 27 '17 at 12:24

How to sort table according to multiple columns at a time?

2 Answers2