Mysql – Complex thesql selection

MySQLselect

I have the following table,

id | booki_id | bet_id   |      bet  | bettype | line | odds 
1  |   123    |   321    |        1  |    3way |  0,0 |  2.3
2  |   123    |   321    |        2  |    3way |  0,0 |  3.4
3  |   123    |   322    |        1  |    3way |  0,0 |  1.1
4  |   123    |   322    |        2  |    3way |  0,0 |  7.4
5  |   123    |   323    |        1  |    3way |  0,0 |  1.3
6  |   123    |   323    |        2  |    3way |  0,0 |  9.4

How can I get the following array? Or can I get wit one selection?

array
(
[0]=>array
(
   "booki_id"=>123,
   "betid"=>321,
   "bet"=>array([1]=>2.3
                [2]=>3.4
                ),
    "bettype"=>"3way",
    "line"=>0
),
[1]=>array
(
   "booki_id"=>123,
   "betid"=>323,
   "bet"=>array([1]=>1.3
                [2]=>9.4
                ),
    "bettype"=>"3way",
    "line"=>0
)
[2]=>array
(
   "booki_id"=>123,
   "betid"=>322,
   "bet"=>array([1]=>1.1
                [2]=>7.4
                ),
    "bettype"=>"3way",
    "line"=>0
)
)

And It's ordered by bet[1];

UPDATED:

id | booki_id | bet_id   |      bet  | bettype | line | odds 
1  |   123    |   321    |        1  |    3way |  0,0 |  2.3
2  |   123    |   321    |        2  |    3way |  0,0 |  3.4
3  |   123    |   321    |        x  |    3way |  0,0 |  2.4
4  |   123    |   322    |        1  |    3way |  0,0 |  1.1
5  |   123    |   322    |        2  |    3way |  0,0 |  7.4
6  |   123    |   322    |        x  |    3way |  0,0 |  3.4
7  |   123    |   323    |        1  |    3way |  0,0 |  1.3
8  |   123    |   323    |        2  |    3way |  0,0 |  9.4
9  |   123    |   323    |        x  |    3way |  0,0 |  6.4

And sometimes I have to order by bet[2] or bet[x];

Best Answer

I tried to produce the same ordering and having some trouble but here are two different queries you can start with:

SELECT
    booki_id,bet_id,bettype,
    group_concat(odds order by bet) betodds,line
FROM
    bettable
GROUP BY
    booki_id,bet_id,bettype,line
;

SELECT * FROM
(
    SELECT
        booki_id,bet_id,bettype,
        group_concat(odds order by bet) betodds,line
    FROM
        bettable
    GROUP BY
        booki_id,bet_id,bettype,line
) AA
ORDER BY betodds
;

I also created sample data to test it with, and here it is:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.12 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

MySQL (Current test) :: use test
Database changed
MySQL (Current test) :: DROP TABLE IF EXISTS bettable;
Query OK, 0 rows affected (0.03 sec)

MySQL (Current test) :: CREATE TABLE bettable
    -> (
    ->     id       INT NOT NULL AUTO_INCREMENT,
    ->     booki_id INT,
    ->     bet_id   INT,
    ->     bet      INT,
    ->     bettype  VARCHAR(10),
    ->     line     FLOAT,
    ->     odds     FLOAT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

MySQL (Current test) :: INSERT INTO bettable (booki_id,bet_id,bet,bettype,line,odds) VALUES
    -> (123,321,1,'3way',0.0,2.3),
    -> (123,321,2,'3way',0.0,3.4),
    -> (123,322,1,'3way',0.0,1.1),
    -> (123,322,2,'3way',0.0,7.4),
    -> (123,323,1,'3way',0.0,1.3),
    -> (123,323,2,'3way',0.0,9.4);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

MySQL (Current test) :: SELECT
    ->     booki_id,bet_id,bettype,
    ->     group_concat(odds order by bet) betodds,line
    -> FROM
    ->     bettable
    -> GROUP BY
    ->     booki_id,bet_id,bettype,line
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.01 sec)

MySQL (Current test) :: SELECT * FROM
    -> (
    ->     SELECT
    ->         booki_id,bet_id,bettype,
    ->         group_concat(odds order by bet) betodds,line
    ->     FROM
    ->         bettable
    ->     GROUP BY
    ->         booki_id,bet_id,bettype,line
    -> ) AA
    -> ORDER BY betodds
    -> ;
+----------+--------+---------+---------+------+
| booki_id | bet_id | bettype | betodds | line |
+----------+--------+---------+---------+------+
|      123 |    322 | 3way    | 1.1,7.4 |    0 |
|      123 |    323 | 3way    | 1.3,9.4 |    0 |
|      123 |    321 | 3way    | 2.3,3.4 |    0 |
+----------+--------+---------+---------+------+
3 rows in set (0.02 sec)

MySQL (Current test) ::

You will have to play with the order of the whole rows from here.

Give it a Try !!!

Related Question