Websphere Websphere - 16 days ago 8
MySQL Question

1 select query to sort rows according to 2 columns?

I'm wondering if it's possible to achieve something with one query. Here is my table structure knowing thay (id, id_attached) are a UNIQUE INDEX

id id_attached name
1 0 Name of row 1
2 0 Name of row 2
3 1 Name of row 3
4 1 Name of row 4
2 1 Name of row 5


Is it possible with one query to select rows in this order. I'll need to use a GROUP BY id but at the same time i'll need 2 separate rows for the row 2?

1 Name of row 1
3 Name of row 3
4 Name of row 4
2 Name of row 2


Thanks a lot for your help and hope I'm clear

Answer

You can sort like this:

SELECT *
from my_table
ORDER BY IF(`id_attached` is NULL, id, `id_attached`),id;

and for you second question:

SELECT id , `id_attached`,name
from my_table
group by id
ORDER BY IF(`id_attached` = 0, id, `id_attached`),id;

Sample

mysql> select * from my_table;
+------+-------------+---------------+
| id   | id_attached | name          |
+------+-------------+---------------+
|    1 |        NULL | Name of row 1 |
|    2 |        NULL | Name of row 2 |
|    3 |           1 | Name of row 3 |
|    4 |           1 | Name of row 4 |
+------+-------------+---------------+
4 rows in set (0,00 sec)

mysql>
mysql>
mysql> SELECT * from my_table
    -> ORDER BY IF(`id_attached` is NULL, id, `id_attached`),id;
+------+-------------+---------------+
| id   | id_attached | name          |
+------+-------------+---------------+
|    1 |        NULL | Name of row 1 |
|    3 |           1 | Name of row 3 |
|    4 |           1 | Name of row 4 |
|    2 |        NULL | Name of row 2 |
+------+-------------+---------------+
4 rows in set (0,00 sec)

mysql>