mysql order by select -- possible?

Question

Here is my table setup:

mysql> describe a;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment | 
| x     | int(11)    | YES  |     | NULL    |                | 
| y     | varchar(1) | YES  |     | NULL    |                | 
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> describe b;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | int(11)    | NO   | PRI | NULL    | auto_increment | 
| a     | int(11)    | NO   |     | NULL    |                | 
| z     | varchar(1) | YES  |     | NULL    |                | 
+-------+------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> select * from a;
+----+------+------+
| id | x    | y    |
+----+------+------+
|  1 |    1 | a    | 
|  2 |    2 | b    | 
|  3 |    3 | c    | 
|  4 |    4 | d    | 
+----+------+------+
4 rows in set (0.01 sec)

mysql> select * from b;
+----+---+------+
| id | a | z    |
+----+---+------+
|  1 | 3 | q    | 
|  2 | 2 | a    | 
|  3 | 1 | u    | 
|  4 | 4 | x    | 
+----+---+------+
4 rows in set (0.01 sec)

Here's what I want to do:

mysql> select a.* from a, b where a.id = b.a order by b.z;
+----+------+------+
| id | x    | y    |
+----+------+------+
|  2 |    2 | b    | 
|  3 |    3 | c    | 
|  1 |    1 | a    | 
|  4 |    4 | d    | 
+----+------+------+
4 rows in set (0.00 sec)

However, I want to use a syntax like "SELECT * FROM a ORDER BY (SELECT a FROM b ORDER BY z)".

Is this possible?

Answer 1

I really don't think that it's possible the way you describe it, I think that you will need to to join the tables.

Look this up : http://www.w3schools.com/Sql/sql_join_inner.asp

Answer 2

SELECT * FROM a ORDER BY (SELECT a FROM b ORDER BY z)

No, this will not get you what you want. In the first place, the subquery returns more than one row, so it can't be used as an expressions in the ORDER BY of the outer query. This is the error you get:

ERROR 1242 (21000): Subquery returns more than 1 row

Also, there's no correlation between the rows of a and the order of the rows in the subquery, given that form of query. In other words, even if you've sorted the values in the subquery, that doesn't make the rows of the outer query sort in a matching order.

If you're trying to ensure only a single row in the result set per row if a (even when there are multiple matching rows in b), then you probably want this:

SELECT DISTINCT a.* 
FROM a JOIN b ON (a.id = b.a)
ORDER BY b.z;