Sort array of Rails of type MySQL::Result

Question

I have an array of results which I get from SQL by using ActiveRecord::Base.connection.execute command. (I cannot use ActiveRecord queries like Object.where() because the table has no corresponding model)

I get an array as:

2800  1.7  1
2000  4.5  0
2107  3.0  0
2107  5.6  1
3435  2.3  0
3425  0.0  0
3425  1.3  0
1980  2.0  0

Now I want to sort this 2D array by the values in the 3rd column, so that all rows with a value of 1 in 3rd column move to the bottom, but want to keep all the rows with the same value in the 1st column to be together.

The resulting sorted array should look like this:

2000  4.5  0
3435  2.3  0
3425  0.0  0
3425  1.3  0
1980  2.0  0
2800  1.7  1
2107  3.0  0
2107  5.6  1

Now I know this can be done by using a sum query on the 3rd column or by using the sort_by method on the array, but I'm unable formulate a correct query. Any pointers on how to proceed will be appreciated.

that is what I want to know - How do I formulate a query so that all rows with a value of 1 in the 3rd column are moved down along with other rows with a same value in 1st column. (as in the example, both rows containing 2107 are moved down, since one of them has a value of 1 in the 3rd column) — tekina
– tekina, Commented Sep 11, 2015 at 10:49
Why don't you just add a (readonly) ActiveRecord model for that table? — spickermann
– spickermann, Commented Sep 11, 2015 at 10:54
@spickermann unfortunately that's not my decision to make. @AmitBadhekaPykihStaff it's just the basic select * from TABLE_NAME order by 3rd_COLUMN right now — tekina
– tekina, Commented Sep 11, 2015 at 10:57

Aleksey Shein · Accepted Answer · 2015-09-11 11:11:32Z

Via SQL:

( SELECT t.* FROM mytable t 
LEFT JOIN mytable tt ON t.a = tt.a AND tt.c = 1 
WHERE tt.a IS NULL) 
UNION 
( SELECT t.* FROM mytable t 
  JOIN mytable tt ON t.a = tt.a AND tt.c = 1 
  ORDER by t.c, t.a
)

Here I splitted the query into 2 parts: the second part searches all records with 3rd column being 1 and all their neighbours by 1st column, i.e.

> (select t.* from mytable t join mytable tt ON t.a = tt.a AND tt.c = 1 ORDER by t.c, t.a);
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 2107 |    3 |    0 |
| 2107 |    6 |    1 |
| 2800 |    2 |    1 |
+------+------+------+
3 rows in set (0.00 sec)

The first part just negates it, i.e. searches everything else. After that I just put them in right order via UNION clause.

Seems to work fine:

mysql> (select t.* from mytable t left join mytable tt ON t.a = tt.a AND tt.c = 1 WHERE tt.a IS NULL) UNION (select t.* from mytable t join mytable tt ON t.a = tt.a AND tt.c = 1 ORDER by t.c, t.a);
+------+------+------+
| a    | b    | c    |
+------+------+------+
| 2000 |    5 |    0 |
| 3435 |    2 |    0 |
| 3425 |    0 |    0 |
| 3425 |    1 |    0 |
| 1980 |    2 |    0 |
| 2800 |    2 |    1 |
| 2107 |    3 |    0 |
| 2107 |    6 |    1 |
+------+------+------+
8 rows in set (0.00 sec)

Amit Badheka · Accepted Answer · 2015-09-11 10:57:20Z

1

You can try handling it in ruby by the following code

array = [[2800 , 1.7,  1],[2000 , 4.5,  0],[2107 , 3.0,  0],[2107 , 5.6,  1],[3435 , 2.3,  0],[3425 , 0.0,  0],[3425 , 1.3,  0],[1980 , 2.0,  0]]

array.sort_by {|a| a[2]}
 # => [[1980, 2.0, 0], [2000, 4.5, 0], [2107, 3.0, 0], [3425, 1.3, 0], [3425, 0.0, 0], [3435, 2.3, 0], [2107, 5.6, 1], [2800, 1.7, 1]]

answered Sep 11, 2015 at 10:57

Amit Badheka

2,6724 gold badges21 silver badges29 bronze badges

2 Comments

tekina Over a year ago

But I want to keep the rows with the same value in 1st column together. In your example, rows with values 2107 are split.

Amit Badheka Over a year ago

Yes, sorry i did not read the question properly. I will make an edit

Collectives™ on Stack Overflow

Sort array of Rails of type MySQL::Result

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related